vba to add fields to pivot table, needs work

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
Did you try the updated macro of post # 2?
Did you have any problems or just want another help?
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
oops...didnt see that post. sorry. let me try now
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
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"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
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
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
wow, beautiful...thanks so much Dante.

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
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.
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
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
 

Forum statistics

Threads
1,089,297
Messages
5,407,443
Members
403,143
Latest member
CTremblay

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top