cjmitch427
New Member
- Joined
- Nov 6, 2015
- Messages
- 1
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.Caption
Next n
End With
End With
With .PivotTables("PivotTable2").PivotFields("ID")
For n = 1 To i
.PivotItems.Caption = a(n, 1)
Next n
End With
End With
End Sub
But it kept getting hung up with : .PivotItems.Caption = a(n, 1)
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.Caption
Next n
End With
End With
With .PivotTables("PivotTable2").PivotFields("ID")
For n = 1 To i
.PivotItems.Caption = a(n, 1)
Next n
End With
End With
End Sub
But it kept getting hung up with : .PivotItems.Caption = a(n, 1)