Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: vba to add fields to pivot table, needs work

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba to add fields to pivot table, needs work

    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/macr...-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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    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
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    can anyone help?

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    Did you try the updated macro of post # 2?
    Did you have any problems or just want another help?
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    oops...didnt see that post. sorry. let me try now

  6. #6
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    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"

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    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" 'not to touch these pivot table (capitalize names)
          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
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    wow, beautiful...thanks so much Dante.

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

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    Quote Originally Posted by rishijain11 View Post
    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.
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to add fields to pivot table, needs work

    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?



    Quote Originally Posted by DanteAmor View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •