vba to add fields to pivot table, needs work

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
hi, i'm using the below code to add fields to my pivot table. it works great, but my problem is it only works for one pivot table on each page.

can someone help me edit this so that if there are multiple pivots on the same sheet?

if it helps, i'm just using the code this guy created in the "Pivot Table Fields Macro Buttons.xlsm" file here:
https://www.excelcampus.com/vba/macro-buttons-pivot-table-fields/



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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try this

Code:
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
  For Each pt In ActiveSheet.PivotTables
    '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
  Next
End Sub
 
Upvote 0
Did you try the updated macro of post # 2?
Did you have any problems or just want another help?
 
Upvote 0
i have 3 pivot tables on my worksheet. is there a way i can get identify, by name, which pivot tables are affected when i press each button?
or, is there a way i can indicate for the macro not to touch a pivot table named "V5"
 
Upvote 0
Try this

Code:
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, pf As PivotField
  Dim sField As String, shp As Shape
  Dim sName As String
  'Set variables
  For Each pt In ActiveSheet.PivotTables
    sName = pt.Name
    Select Case UCase(sName)
      Case "V5", "V6", "ETC" [COLOR=#0000ff]'not to touch these pivot table (capitalize names)[/COLOR]
      Case Else
        Set shp = ActiveSheet.Shapes(Application.Caller)
        sField = shp.TextFrame.Characters.text
        'Toggle field
        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 Select
  Next
End Sub
 
Upvote 0
wow, beautiful...thanks so much Dante.

i noticed you code very cleanly, was wondering if you had any youtube videos to recommend?
 
Upvote 0
wow, beautiful...thanks so much Dante.

i noticed you code very cleanly, was wondering if you had any youtube videos to recommend?

I don't have any particular video.
When I have doubts I search on google ;)


Glad to help you, thanks for the feedback.
 
Upvote 0
Hey Dante, I added another pivot table to the sheet, and when i use the buttons now, i'm getting this error:

"Unable to set the Orientation property of the PivotField class"

do you know why?



Try this

Code:
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
  For Each pt In ActiveSheet.PivotTables
    '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
  Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top