Help! I'm trying to change the grouped names in all my pivots to be the same using VPA script

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(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)
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,215,219
Messages
6,123,688
Members
449,117
Latest member
Aaagu

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