VBA - Pivot Table Toggle Data field

[biggsy3]

Hi all,

I'm currently using this code to toggle pivot table row fields, there are buttons on my spreadsheet that I click to add/remove row fields in the pivot table.

This works great, however I was wandering if it is possible to adapt the code to toggle pivot table data field "values" by average.

I've played around with the code and i can get it to semi work.

I changed all the Rowfields to Datafields however the data just duplicates over and over and doesn't remove the previous field selection.


As you can probably see I am a complete novice to VBA, any help would be much appreciated. I have been scouring the net for answers with no joy so after a day of scratching my head I'm looking for someone to point me in the right direction.


Thank you in advance.



Sub Toggle_Row_Field()
'Add/Remove the field to the pivot table.
'The field is determined by the button text that calls the macro.


Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Dim shp As Shape


'Set variables
Set pt = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text

'Toggle field
'If visible then hide it
If pt.PivotFields(sField).Orientation = xlRowField Then
pt.PivotFields(sField).Orientation = xlHidden
shp.Fill.ForeColor.Brightness = 0.5
Else 'Add to Rows area
pt.PivotFields(sField).Orientation = xlRowField
shp.Fill.ForeColor.Brightness = 0
End If


End Sub

 

[biggsy3]

Any other way around this?

 

[Damkilde1986]

Hello Biggsy3,
I have same issue. And I have not been able to find any solutions to this issue.

Did you succed somehow?

Best regards,
Daniel

 

[Doug Johnson]

Here is the code I run on large PTs to bulk change the default setting.
Public Sub PTAB___FieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie; Mods by Doug Johnson

'xlAverage -4106
'xlCount -4112
'xlCountNums -4113
'xlMax -4136
'xlMin -4139
'xlProduct -4149
'xlStDev -4155
'xlStDevP -4156
'xlSum -4157
'xlUnknown 1000
'xlVar -4164
'xlVarP

Dim SubTotalType As String
Dim pf As PivotField

'Add a user input box with default value xlSum
SubTotalType = InputBox("What type of summary do you want? Options are: xlSum, xlAverage, xlCount, xlMax, xlMin", "Summary Type", "xlSum")

With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
'.Function = xlSum

'Cycle through predefined summary types
If SubTotalType = "xlMin" Then
.Function = xlMin
ElseIf SubTotalType = "xlCount" Then
.Function = xlCount
ElseIf SubTotalType = "xlAverage" Then
.Function = xlAverage
ElseIf SubTotalType = "xlMax" Then
.Function = xlMax
Else
.Function = xlSum
End If

.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub