Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table ??
I've got a pivot table, one on each sheet. Both pivot tables display similar
information, but in slightly different ways. Question is, can I set it up so BOTH pivot tables respond to changes in the "page" dimension of one pivot table. It would be nice to change the "Year" dimension in PT1 and have that year carry across to PT2. Currently, I have to change the year in BOTH pivot tables manually. Any ideas? |
#2
|
|||
|
|||
You may be able to do this with a Macro or VBA, but, AFAIK there is no Pivot
Table feature that allows you to "synchronize" separate Pivot Tables. |:) "Kirk P." wrote: I've got a pivot table, one on each sheet. Both pivot tables display similar information, but in slightly different ways. Question is, can I set it up so BOTH pivot tables respond to changes in the "page" dimension of one pivot table. It would be nice to change the "Year" dimension in PT1 and have that year carry across to PT2. Currently, I have to change the year in BOTH pivot tables manually. Any ideas? |
#3
|
|||
|
|||
The following code is adapted from a posting by Robert Rosenberg. It
changes the second Pivot Table if the page is changed on the first PT. You could revise it to suit your layout. As noted in the code, place the code on the module for the worksheet which contains the Pivot Table (right-click the sheet tab, choose View Code). Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() 'by Robert Rosenberg 2000/01/11 ''I use a module level variable to keep track of ''the last selection from the Page Field. ''This routine was place in the Worksheet ''containing the PivotTable's code module. Dim pvt As PivotTable Dim pvt2 As PivotTable Set pvt = Me.PivotTables(1) Set pvt2 = Sheets("OtherPivot").PivotTables(1) If LCase(pvt.PivotFields("Customer").CurrentPage) _ < LCase(mvPivotPageValue) Then 'The Page Field was changed Application.EnableEvents = False pvt.RefreshTable mvPivotPageValue = _ pvt.PivotFields("Customer").CurrentPage pvt2.PageFields("Customer").CurrentPage _ = mvPivotPageValue Application.EnableEvents = True End If End Sub Kirk P. wrote: I've got a pivot table, one on each sheet. Both pivot tables display similar information, but in slightly different ways. Question is, can I set it up so BOTH pivot tables respond to changes in the "page" dimension of one pivot table. It would be nice to change the "Year" dimension in PT1 and have that year carry across to PT2. Currently, I have to change the year in BOTH pivot tables manually. Any ideas? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
That's what I'm looking for - Thanks!
"Debra Dalgleish" wrote: The following code is adapted from a posting by Robert Rosenberg. It changes the second Pivot Table if the page is changed on the first PT. You could revise it to suit your layout. As noted in the code, place the code on the module for the worksheet which contains the Pivot Table (right-click the sheet tab, choose View Code). Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() 'by Robert Rosenberg 2000/01/11 ''I use a module level variable to keep track of ''the last selection from the Page Field. ''This routine was place in the Worksheet ''containing the PivotTable's code module. Dim pvt As PivotTable Dim pvt2 As PivotTable Set pvt = Me.PivotTables(1) Set pvt2 = Sheets("OtherPivot").PivotTables(1) If LCase(pvt.PivotFields("Customer").CurrentPage) _ < LCase(mvPivotPageValue) Then 'The Page Field was changed Application.EnableEvents = False pvt.RefreshTable mvPivotPageValue = _ pvt.PivotFields("Customer").CurrentPage pvt2.PageFields("Customer").CurrentPage _ = mvPivotPageValue Application.EnableEvents = True End If End Sub Kirk P. wrote: I've got a pivot table, one on each sheet. Both pivot tables display similar information, but in slightly different ways. Question is, can I set it up so BOTH pivot tables respond to changes in the "page" dimension of one pivot table. It would be nice to change the "Year" dimension in PT1 and have that year carry across to PT2. Currently, I have to change the year in BOTH pivot tables manually. Any ideas? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
pivot table | Excel Worksheet Functions | |||
Removing errors from a Pivot table | Excel Discussion (Misc queries) | |||
Pivot Table services | Excel Discussion (Misc queries) | |||
Problem with Pivot Table Drop-Down Menus | Excel Worksheet Functions |