Toggle SUM/AVG on Pivot Table

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:

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!

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

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.

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.

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:

Code:

---

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

---

Some references and structure and objects and logic were wrong

Code:

---

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

---

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

Code:

---

For Each pf In ActiveCell.PivotTable.DataFields

---

Fantastic! Thank you.