VBA Script for changing grouped data names in multiple pivot tables

[cjmitch427]

I have multiple pivot tables on multiple tabs all with the same data source. I have manually grouped some of the row items together, but all other pivot tables say "Group1", "Group2", etc. I changed the name in one of the pivots, but the others don't change. I'd like to set up a macro/vba script to change all other pivot tables' grouped names when I change the name on the first pivot. Does anyone have some script I can use? I tried this:

Sub GroupText()
Dim n As Integer
Dim i As Integer
Dim a As Variant

Application.ScreenUpdating = False

With Sheets("Sheet1")
    With .PivotTables("PivotTable1")
        With .PivotFields("ID")
            i = .PivotItems.Count
            ReDim a(i, 1)
            For n = 1 To i
                a(n, 1) = .PivotItems(n).Caption
            Next n
        End With
    End With

    With .PivotTables("PivotTable2").PivotFields("ID")
        For n = 1 To i
            .PivotItems(n).Caption = a(n, 1)
        Next n
    End With
End With
End Sub

But it kept getting hung up with : .PivotItems(n).Caption = a(n, 1)

[SamT]

Arrays start counting at zero, unless you have "Option Base 1" at the top of the code page.
Why use a 2 dimensional array when you only have 1 dimension to store?

If that is the entire sub then run this first of all

Sub ResetScreenUpdating()
'cuz you left it turned off
Application.ScreenUpdating = True
End Sub

This is how I would write your version of the code

Sub GroupText()
   Dim i As Integer
    Dim a As Variant
     
    'Application.ScreenUpdating = False 'Uncomment after testing
     
    With Sheets("Sheet1")
        With .PivotTables("PivotTable1")
            With .PivotFields("ID")
                ReDim a(.PivotItems.Count - 1) 'Start counting at zero
                For i = 0 To UBound(a)
                    a(i) = .PivotItems(i + 1).Caption
                Next i
            End With
        End With
         
        With .PivotTables("PivotTable2").PivotFields("ID")
            For i = LBound(a) To UBound(a) 'Another way of doing it.
                .PivotItems(i + 1).Caption = a(i)
            Next i
        End With
    End With
    Application.ScreenUpdating = True
End Sub

And what my version would look like

Option Explicit

Sub VBAX_SamT_GroupText()
'For Help see: http://www.vbaexpress.com/forum/showthread.php?54221
  Dim i As Integer
  Dim PF1 As PivotField
  Dim PF2 As PivotField
  
  With Sheets("Sheet1")
    Set PF1 = .PivotTables("PivotTable1").PivotFields("ID")
    Set PF2 = .PivotTables("PivotTable2").PivotFields("ID")
  End With
  
  'Application.ScreenUpdating = False 'Uncomment after testing
    For i = 1 To PF1.PivotItems.Count
      PF2.PivotItems(i).Caption = PF1.PivotItems(i).Caption
    Next i
    Application.ScreenUpdating = True
End Sub

I am assuming that other than the problems mentioned, your code is good.

[Aflatoon]

Your code works fine for me. Have you stepped through to check what the caption is and to make sure that the correct field/item are being referred to?

[cjmitch427]

Thank you very much, Mark007!

I'm still having an issue though... I first tried using your modifications to my code and was still having an issue... So I used your code to see if it would work better and I'm still getting hung up on:
PF2.Pivotitems(I).caption = PF1.PivotItems(I).caption
When I move the curser over the (I), it says it is hung up on I=6. Any ideas on what could be causing this?

My data set contains different job titles, and I have these job titles grouped into categories. For example: Clinician I, Clinician II, & Sr Clinician are all grouped as "Clinicians". The default group name is "Group2", so I want to change the name in PF1, and run a macro to change the name in all other pivot tables with the same data set. There are approximately 12 grouped categories. I don't know if any of that additional data helps...

Any help you can give me would be very appreciated! I'm "OK" with VBA, but I'm not good enough to write my own script. And anything I usually do takes me a loooooooooooooooong time!