XLA routines: EE_ApplyPivotDataFormatting

Nick's picture
Takes a pivot table, and formats the pivot field based on a 2 col range
Sub EE_ApplyPivotDataFormatting(pt As PivotTable, FormattingRange As range)
'- takes a 2 col range, and applies the formatting specified in col 2 to the Data fields.
    Dim rngCell As range
    If FormattingRange.Rows.Count <> 2 Then Exit Sub
    For Each rngCell In FormattingRange.Rows
        pt.PivotFields(rngCell.Cells(, 1).value).NumberFormat = rngCell.Cells(, 2).value
    Next rngCell
    Set rngCell = Nothing
End Sub


I do not know how to set up the value for the variable pt to call the subroutine. Could you please provide a sample of how to determine the parameters for the above function. I would very much like to get this to work. I find it very annoying that you cannot adjust the default formatting of pivot tables. Thank you.

Nick's picture

activesheet.pivottables(1) wl

wld do the trick