Mlemons
12-27-2017, 07:19 AM
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!
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!