How to change the selected numeric PivotFields function to `sum`-CodePudding

How to change the selected numeric PivotFields function to sum ? Below code can't work. Maybe I should judge the field is selected and attribution is numeric first, but I don't know how the code should be.

Sub changeFunction()
Dim pf As PivotField
    For Each pf In ActiveSheet.PivotTables("PivotTable1").PivotFields
     With pf
       .Function = xlSum
     End With
    Next pf
End Sub

CodePudding user response：

You are very close, but there is an issue.

When you cycle through all PivotFields using For..Each.. - it will ignore items in the Values category.

To access those, you need to cycle through the DataFields instead, like so:

Sub changeFunction()
Dim pf As PivotField
    For Each pf In ActiveSheet.PivotTables("PivotTable1").DataFields
     With pf
       .Function = xlSum
     End With
    Next pf
End Sub