excel-vba Pivot Tables Formatting the Pivot Table Data


This example changes/sets several formats in the data range area (DataBodyRange) of the given Pivot Table. All formattable parameters in a standard Range are available. Formatting the data only affects the Pivot Table itself, not the Pivot Cache.

NOTE: the property is named TableStyle2 because the TableStyle property is not a member of the PivotTable's object properties.

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With