sycronize 2 pivot tables

bill_reinwald

New Member
Joined
Sep 19, 2005
Messages
22
I'm trying to use the following code to sychronize 2 or more pivot tables, so that whatever fields that are chosen on pivottable1 are also chosen on the other pivot tables

This code works as long as the pivot field is dragged over to the "page" section of the table, but not if its placed on a column or row. I'd like to be able to put "Months" along the rows, and any time I update Pivot table 1 with a selection of months, the other pivot tables would automatically use teh same selection of months.

ActiveSheet.PivotTables("PivotTable2").PivotFields("Cut off Date"). _
CurrentPage = ActiveSheet.PivotTables("PivotTable1").PivotFields("Cut off Date").CurrentPage.Value

thanks,

bill
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Joined
Jul 30, 2006
Messages
3,656
The following code will synchronize two pivot tables on the same sheet, with changes to the 'Page' field:


'-------------------Code begins here-----------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' Macro created 11/12/2002 by Stanley D. Grom, Jr.
' Synchronize mySlave PivotTable PageField "DATE" with
' myMaster PivotTable PageField "DATE"
'
Dim myMaster As PivotTable
Dim mySlave As PivotTable

Set myMaster = Worksheets("PivotTables").Range("A1").PivotTable

Set mySlave = Worksheets("PivotTables").Range("F1").PivotTable

mySlave.PivotFields("DATE").CurrentPage = myMaster.PivotFields("DATE").CurrentPage.Name

End Sub
'-------------------Code ends here-------------------------------------


Have a great day,
Stan
 

bill_reinwald

New Member
Joined
Sep 19, 2005
Messages
22
Ok, I can't actually get this code to run, but I think its just going to work if the "Date" is a page field, which I already can get to work, but dont' want.

Anyone have an idea on how to synch 2 or more pivot tables so that they all select the same field selections as the 'main' one? So if I have the main showing Month across the top, and Year along the side, and I have 2 other pivot tables with the same layout, but different values in the table's DATA section, I can update all of them at once whenever I change the Month or Year on the main table?
 
Joined
Jul 30, 2006
Messages
3,656
Synchronizing 2 pivot tables with Page and Data Fields

Here is an example of synchronizing two pivot tables on the same worksheet, with the Page Fields 'DATE', and the Data Fields 'STATE', see below:

Here is the myMaster pivot table (sorry, I am having trouble pasting in myMaster - it looks just like mySlave, but it starts in cell A1):

Here is the mySlave pivot table:
synchronize pivottables.xls
FGHI
1DATE(All)
2
3SumofUnitsCHANNEL
4STATERetailWholesale
5WA32,902121,115
6AZ7,44117,900
7CA21,79590,590
8ID1,860
9NV37,50914,125
10OR58,721120,795
11UT3,62336,445
12GrandTotal163,851400,970
PivotTables




Here is the modified code:

'------------------Code Begins Here------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Worksheet_SelectionChange(ByVal Target As Range)
' Macro created 09/22/2006 by Stanley D. Grom, Jr.
'
' Synchronize mySlave PivotTable PageField "DATE" with
' myMaster PivotTable PageField "DATE"
'
Dim myMaster As PivotTable
Dim mySlave As PivotTable
'Dim strPgName As String

Set myMaster = Worksheets("PivotTables").Range("A1").PivotTable

Set mySlave = Worksheets("PivotTables").Range("F1").PivotTable

mySlave.PivotFields("DATE").CurrentPage = myMaster.PivotFields("DATE").CurrentPage.Name

'With ActiveSheet.PivotTables("myMaster").PivotFields("STATE")
' .PivotItems("WA").Visible = True
' .PivotItems("AZ").Visible = True
' .PivotItems("CA").Visible = True
' .PivotItems("ID").Visible = True
' .PivotItems("NV").Visible = True
' .PivotItems("OR").Visible = True
' .PivotItems("UT").Visible = True
'End With

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("WA").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("WA").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("WA").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("WA").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("AZ").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("AZ").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("AZ").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("AZ").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("CA").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("CA").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("CA").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("CA").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("ID").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("ID").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("ID").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("ID").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("NV").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("NV").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("NV").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("NV").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("OR").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("OR").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("OR").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("OR").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("UT").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("UT").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("UT").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("UT").Visible = False
End If

End Sub
'------------------Code Ends Here--------------------------------------


I hope this works for you.

I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan
 
Joined
Jul 30, 2006
Messages
3,656
Synchronizing 2 pivot tables with Page and Data Field

Synchronizing 2 pivot tables with Page and Data Field

Here is an example of synchronizing two pivot tables on the same worksheet, with the Page Fields 'DATE', and the Data Fields 'STATE', see below:

Here is the myMaster pivot table:
synchronize pivottables.xls
ABCD
1DATE(All)
2
3SumofUNITSPRICE
4STATEHighLowMid
5AZ3,7239,00912,609
6CA11,12231,50469,759
7ID1934931,174
8NV17,85913,32520,450
9OR32,86673,52173,129
10UT4,60515,21620,247
11WA29,77172,74451,502
12GrandTotal100,139215,812248,870
PivotTables



Here is the mySlave pivot table:
synchronize pivottables.xls
FGHI
1DATE(All)
2
3SumofUnitsCHANNEL
4STATERetailWholesale
5WA32,902121,115
6AZ7,44117,900
7CA21,79590,590
8ID1,860
9NV37,50914,125
10OR58,721120,795
11UT3,62336,445
12GrandTotal163,851400,970
PivotTables




Here is the modified code:

'------------------Code Begins Here------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Worksheet_SelectionChange(ByVal Target As Range)
' Macro created 09/22/2006 by Stanley D. Grom, Jr.
'
' Synchronize mySlave PivotTable PageField "DATE" with
' myMaster PivotTable PageField "DATE"
'
Dim myMaster As PivotTable
Dim mySlave As PivotTable
'Dim strPgName As String

Set myMaster = Worksheets("PivotTables").Range("A1").PivotTable

Set mySlave = Worksheets("PivotTables").Range("F1").PivotTable

mySlave.PivotFields("DATE").CurrentPage = myMaster.PivotFields("DATE").CurrentPage.Name

'With ActiveSheet.PivotTables("myMaster").PivotFields("STATE")
' .PivotItems("WA").Visible = True
' .PivotItems("AZ").Visible = True
' .PivotItems("CA").Visible = True
' .PivotItems("ID").Visible = True
' .PivotItems("NV").Visible = True
' .PivotItems("OR").Visible = True
' .PivotItems("UT").Visible = True
'End With

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("WA").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("WA").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("WA").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("WA").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("AZ").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("AZ").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("AZ").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("AZ").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("CA").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("CA").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("CA").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("CA").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("ID").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("ID").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("ID").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("ID").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("NV").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("NV").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("NV").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("NV").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("OR").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("OR").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("OR").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("OR").Visible = False
End If

If ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("UT").Visible = True Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("UT").Visible = True
ElseIf ActiveSheet.PivotTables("myMaster").PivotFields("STATE").PivotItems("UT").Visible = False Then
ActiveSheet.PivotTables("mySlave").PivotFields("STATE").PivotItems("UT").Visible = False
End If

End Sub
'------------------Code Ends Here--------------------------------------


I hope this works for you.

I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan
 

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182
Can this code be simplified? I am trying to do the same thing with 4 pivots, each in a different sheet within the same workbook. The only field I want to change is the state field, which is in the page section of my table layout.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,033
Members
410,583
Latest member
gazz57
Top