Multiple Pivot Tables
Posted by A on April 13, 2001 8:23 AM
I have a worksheet that is linked back to 12 separate pivot tables - the idea is to bring all the top items on 12 separate ranking reports into something that looks like one report. Every pivot table has the same two items in the "page" field. is there a way i can allow the user to choose these two items only once, and it will universally choose the items for all 12 pivot tables? I was thinking maybe with a pull down menu or something. Any help would be VERY VERY appreciated. Thanks
Posted by Mark W. on April 13, 2001 9:44 AM
There's no built-in way to accomplish this, but
you might want to take a look at one of my earlier
postings...
Posted by mcs on April 13, 2001 10:36 AM
I tried looking at the post, because I recently asked this same questions. It does work, but each time the page heading is changed the other pivot table has to be refreshed in order to change to the new selection. If anyone has a different way to accomplish this it would be GREATLY appreciated.
Posted by Mark W. on April 13, 2001 10:55 AM
Your observation about the need for a Refresh is
indeed correct. I believe this could be addressed
with a custom Refresh VBA that is triggered by the
page field change. I have no doubt that some eager
VBA wizard to "whip-up" code snippet with relative
ease. I tried looking at the post, because I recently asked this same questions. It does work, but each time the page heading is changed the other pivot table has to be refreshed in order to change to the new selection. If anyone has a different way to accomplish this it would be GREATLY appreciated.
Posted by A on April 13, 2001 11:26 AM
i don't understand how this fixes the problem - my scenario is the page options (there are two of them) are time period and market - all 12 of the pivot tables are set up exactly the same, but they're all coming from different product lists. the time period and markets will never change from month to month - but i need to be able to send this file out to many users, some of which will need to see more than one market. is it possible to choose, or even enter the name of the market and time period and assign that a value, which would in turn "pick" the markets and time periods? There's no built-in way to accomplish this, but
Posted by Mark W. on April 13, 2001 12:34 PM
It doesn't matter that you have more than 1 page
field; however, the fact that your PivotTables are
sourced from independent data lists makes it a bit
tricker. Perhaps, it would be better to solicite
a custom VBA that would accomplish your objectives. i don't understand how this fixes the problem - my scenario is the page options (there are two of them) are time period and market - all 12 of the pivot tables are set up exactly the same, but they're all coming from different product lists. the time period and markets will never change from month to month - but i need to be able to send this file out to many users, some of which will need to see more than one market. is it possible to choose, or even enter the name of the market and time period and assign that a value, which would in turn "pick" the markets and time periods? : There's no built-in way to accomplish this, but
Posted by Dave Hawley on April 13, 2001 9:49 PM
Mark, you naughty little boy! You told me you knew VBA, were you telling fibs?
Hi A
Here is some VBA code that will change ALL pivot Tables in the Workbook to have the same Page Fields as the one you change.
To put in the code, Push Alt+F11, then go to Insert>Module and paste in this:
Sub ChangeAllPivotHeaders()
Dim SPgField As String, SPgField2 As String
'Written by OzGrid Business Applications
'www.ozgrid.com
SPgField1 = Selection
SPgField2 = Selection.Offset(1, 0)
Dim Pt As PivotTable
Dim Wsht As Worksheet
On Error Resume Next
For Each Wsht In ThisWorkbook.Worksheets
For Each Pt In Wsht.PivotTables
Pt.PageFields(1).CurrentPage = SPgField1
Pt.PageFields(2).CurrentPage = SPgField2
Next Pt
Next Wsht
End Sub
Now push Alt+Q and save.
Push Alt+F8 and select "ChangeAllPivotHeaders", then click "Options" and assign a shortcut key.
Now select the TOP current Page Field Item in any Pivot Table and click the drop down arrow and change it to whatever. With the same cell still selected (TOP current Page Field Item)click the drop arrow for the second Page Field and change it's page field item to whatever. Now push your shortcut key.
Dave
OzGrid Business Applications
