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!