Set all Pivot values to SUM and correct FORMAT

    Browse: Home / Set all Pivot values to SUM and correct FORMAT

When adding new fields to the value part of your pivot table, they automatically set to COUNT, where most of the time you require a SUM. Changing all fields manually (and also correcting the format) is irritating and time consuming.

Hence the following macro, which changes the setting of each field in your value list to SUM and makes it look pretty!

Sub SumAllValueFieldsPivot()
 
Dim pt As PivotTable
Dim ps As PivotField
Dim ws As Worksheet
 
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Application.ScreenUpdating = False
 
pt.ManualUpdate = True
For Each pf In pt.DataFields
pf.Function = xlSum
pf.NumberFormat = "#,##0_ ;[Red]-#,##0 "
Next pf
pt.ManualUpdate = False
 
Application.ScreenUpdating = True
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
 
 
End Sub
«
»