sycronize 2 pivot tables

B

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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA Filter Pivot Table Question
Replies
0
Views
110
gmazza76
G
W
  • Solved
Multiple pivot tables on the same sheet
Replies
2
Views
263
Woftae
W
Gringoire
  • Question
Filtering on a pivot by dates (with some constraints)
Replies
0
Views
315
Gringoire
Gringoire
C
  • Question
Loop through pivot table filter
Replies
1
Views
512
DanteAmor
DanteAmor
C
  • Question
Creating a button to select from a Pivot table filter
Replies
1
Views
425
Micron
M

Forum statistics

Threads
1,214,534
Messages
6,120,086
Members
448,944
Latest member
sharmarick

Share this page

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