excel-vba Pivot Tables Formatting the Pivot Table Data
Example
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
