Excel_Fool
New Member
- Joined
- Apr 10, 2015
- Messages
- 15
I have the following VBA which works with a normal pivot table. It uses the text of the shape to reference the variable in the pivot table. I have rewritten it for use with a pivot table created by Power Pivot (with multiple tables), and it successfully removes all existing fields as in the first loop. However, I don't know how to write the last command (" 'Add field that button was clicked for"). Anyone know how to do this?
VBA for normal pivot table
VBA for Power Pivot table with multiple tables...
VBA for normal pivot table
VBA Code:
Sub Swap_Value()
'The field is determined by the button text that calls the macro.
Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
'Set variables
Set pt = ActiveSheet.PivotTables(1)
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
'Remove existing fields
For Each pf In pt.DataFields
If pf.Name <> "Values" Then
pf.Orientation = xlHidden
End If
Next pf
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlDataField
End Sub
VBA for Power Pivot table with multiple tables...
VBA Code:
Sub swap_values_power_pivot()
Dim ptMain As PivotTable
Dim pfMeasure As CubeField
Dim sField As String
'Set variables
Set ptMain = ActiveSheet.PivotTables(1)
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
'Remove existing fields
For Each pfMeasure In ptMain.CubeFields
If pfMeasure.Orientation = xlDataField Then
pfMeasure.Orientation = xlHidden
End If
Next
'Add field that button was clicked for
ptMain.AddDataField ptMain.CubeFields("[Measures].[" & sField & "]")
End Sub