PDA

View Full Version : VBA Script for changing grouped data names in multiple pivot tables



cjmitch427
11-06-2015, 10:39 AM
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
11-07-2015, 07:23 AM
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
11-09-2015, 03:22 AM
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
11-09-2015, 02:14 PM
Thank you very much, Mark007! :hi:

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! :banghead: