On my Contextures website, there are a few sample files that let you filter one pivot table, and automatically change all the other pivot tables. You can find them on the Sample Files page, in the Pivot Table section.
For example, in the worksheet shown below, if you select Pen Set as the Item in pivot table 1, the same item will be selected in pivot table 2.
This works with Excel VBA Event code, that runs automatically when either pivot table is updated.
Limit the Filter Fields
This code works well, if you want to change all of the fields in all of the workbook’s pivot tables. However, you might have a different worksheet for each Region’s pivot table. If someone changes a Region on one sheet, you don’t want it to automatically change all the other sheets.
In the latest version of the Change Multiple Pivot Tables Automatically workbook, I’ve added a sheet where you can select the specific fields that you want to include. In the screen shot shown below, the Master List pivot table only has Date and Item in its Report Filter area.
Change the Filter
With the revised code, if you filter one of the pivot tables for Region, only that pivot table will be affected, because Region is not in the master list. If you filter for Item or Date, all the pivot tables in the workbook will be updated with the selected item.
In the screen shot below, Region is being changed in the worksheet at the left. The change will not affect the worksheet on the right. However, if the Date is filtered in either pivot table, it will automatically update the other pivot tables in the workbook.
Download the Sample File
To see the code and test the macro, please visit the Excel Sample Files page, and go to the Pivot Tables section. Look for PT0028 – Change Specific Page Fields with Multiple Selection Settings, and download the zipped file.
The file is in Excel 2007/2010 format, and it contains macros. Be sure to enable macros if you want to test the code.
__________________
Hi Debra. Great idea. I know you know this, but it bears repeating here for readers that might not: IF you have Excel 2010, then slicers allow you to do this right out of the box, because for each slicer, you can specify which pivot tables it controls.
I’m a recent convert to slicers…I’ve been using excel 2010 for 2 years, but have only now realized how powerful they are.
On the downside, slicers can take up quite a bit of screen real-estate (although if you have the Microsoft PowerPivot addin installed, they are much improved)
On the upside, slicers address your pivotfields directly. That is, you don’t have to iterate through each field in each pivot on each sheet – which can take a heck of a lot of time if you’ve got pivots with a couple of hundred thousand items in them.
Again, mighty fine post, and certainly very timely for those Luddites with pre excel 2010 installed.
@Jeff, thanks for the reminder about slicers. They’re a great option for those with Excel 2010, and enough space to use them.
Sorry, forgot to add a caveat about using slicers to accomplish the kind of filtering your code does…slicers only work with pivots that share the same pivot cache.
I find it really useful and save me a lot of time.
However, I encountered a problem: When I refresh the the Pivot Table, the selected Page Field changes back to “ALL”. Any idea on this and how to avoid this happen?
Appreciate your help!