Toggle SUM/AVG on Pivot Table

[Mlemons]

Hi All,

I am trying to create a toggle for pivot table that will change the Grand Total column from Sum to Average and back again on command. However, I can't get it to select the entire column. Here is the code:

Sub ToggleCountSum()
    
    Dim pf As PivotField
    
    On Error Resume Next
        Set pf = ActiveCell.PivotField
    On Error GoTo 0
    
    If Not pf Is Nothing Then
        If pf.Function = xlAverage Then
            pf.Function = xlSum
        Else
            pf.Function = xlAverage
        End If
    End If
    
End Sub

How can I make it change the whole table instead of the active cell? Also, the pivot table changes in size depending on what splicers i have on (just fyi).

Thank you!

[Aflatoon]

That code will toggle the data field you have selected. I'm not sure how that differs from what you want?

[Mlemons]

The code only toggles for the active cell. I was hoping to toggle for the whole pivot table. Ideally i will have a command button on the adjacent worksheet that i can hit and everything in the grand total column will toggle back and forth from sum to avg.

[Aflatoon]

No it doesn't. It will toggle the function for the relevant pivot field and not just a specific cell. A grand total column always uses the same function as its detail columns.

[Mlemons]

Right, I see what you are saying. So is there a way I can toggle the function for all pivot fields? I keep trying to put in a pt reference instead of pf but it does not work:

  Dim pt As PivotTable
    
    On Error Resume Next
        Set pt = ActiveSheet.PivotTable
    On Error GoTo 0

[Paul_Hossler]

Some references and structure and objects and logic were wrong

Option Explicit

Sub ToggleFields()
    Dim pf As PivotField
    
    For Each pf In ActiveSheet.PivotTables(1).DataFields
        If pf.Function = xlAverage Then
            pf.Function = xlSum
        ElseIf pf.Function = xlSum Then
            pf.Function = xlAverage
        End If
    Next
End Sub

[Aflatoon]

If you want it to work for the currently selected pivot table

For Each pf In ActiveCell.PivotTable.DataFields

[Mlemons]

Fantastic! Thank you.