In Excel 2007, I have a single dataset in the form of an Excel Table from which I want to make multiple pivot tables and pivot charts. I would like to be able to synchronize the 'report filter' field across all of the pivot tables and charts simultaneously, so that by filtering, (for example) the US_Region field to 'Pacific Northwest' on one of the pivot tables would apply that filter to all of the pivot tables and charts in the workbook (which share a single dataset). Is there a way to do this without VBA and, if there is not, what would be the most flexible/least klugy way to do it in VBA?
Thanks
Best Answer
Create a procedure to change all the page fields. This one loops through all the worksheets in the workbook and if the worksheet has a pivot table, it changes the page field to match the page field on the pivot table passed to the sub.
Then place a call to this procedure in the worksheet code itself and pass the pivot table you changed:
Change the name of the pivot field from "Area" to the page field you need, for example "US_Region".
This may not be suitable if people are frequently changing the structure of your pivot tables in other ways, as it will trigger each time the pivot table is changed, including when it is refreshed. It will also give an error if users remove the specified page field.
If the pivot tables are static though, this might do.