PDA

View Full Version : Toggle SUM/AVG on Pivot Table



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!

Aflatoon
12-27-2017, 07:45 AM
That code will toggle the data field you have selected. I'm not sure how that differs from what you want?

Mlemons
12-27-2017, 07:52 AM
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
12-27-2017, 07:57 AM
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
12-27-2017, 08:07 AM
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
12-27-2017, 09:14 AM
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
12-27-2017, 09:40 AM
If you want it to work for the currently selected pivot table


For Each pf In ActiveCell.PivotTable.DataFields

Mlemons
12-27-2017, 10:31 AM
Fantastic! Thank you.