Category: Pivot Table

In Excel 2010, you can use Slicers to change multiple pivot tables. However, you might be working in an earlier version of Excel, or you don’t have room for Slicers on your worksheets.

Instead of Slicers, you can use programming to update multiple pivot tables automatically. In previous posts, I’ve shown how you can select items in one pivot table’s Report Filter fields, and the Report Filter fields for pivot tables on the other worksheets will change to the same selections.

Specific Sheet and Pivot Tables

Jeff Weir has written an updated version of the code, which runs much faster than the previous version. You’ll notice the speed difference especially if you’re working with larger pivot tables.

Also, in this version of the code, you can specify:

• any sheets you DON’T want the macro to check
• any specific pivot tables that you DON’T want the macro to synchronize.

For example, only update the pivot tables on Sheet1 and Sheet2, and ignore PivotTable2 on Sheet1.
[Update: Sept 20, 2012] Jeff has made the following changes to the code:

• you can now exclude particular PivotFields, plus if you change a pagefield in any pivot, the code will not only update pagefields to the same settings in other pivots but also change rowfields too.
• added basic error handling so that ScreenUpdating and EnableEvents are restored to TRUE if anything goes wrong.

Jeff is also working on a version of the code for Excel 2010, that promises to be even faster — so stay tuned for that!

[Update: June 16, 2013] Jeff has revised the code, so it uses Slicers if the version is Excel 2010 or later.

Making Code Run Faster

In the previous version of the code, it looped through each master pivot field multiple times, to determine if each pivot item is visible or hidden. The corresponding pivot item in each secondary pivot table was then set to the same setting. The code worked, but it was very slow in larger pivot tables.

The main reason that Jeff’s code is faster is that it iterates through each master pivot field just once, so it can record only the visible items into a dictionary.

Then, for each pivot field in each secondary pivot table:

• All the pivot items are made visible
• Items that are not in the dictionary’s list are hidden.

Also, speed in Jeff’s code is increased because it:

• checks to see if.AllItemsVisible = true. If it is, no need to iterate through either the master or the secondary pivot…it just makes all pivot items in the corresponding secondary pivot fields visible. The old code looped through each pivot item
• doesn’t add items to the dictionary for checking if it has already found all the visible pivot items in the master list.

Modify the Code

If you download the sample file (see instructions below), you can copy the code to your own workbooks.

• To see the code in the sample file, go to the Sales Pivot worksheet, right-click the sheet tab, and click View Code.
• Then, to see the full code, right-click on the procedure name – SyncPivotFields – and click Definition

Here is where you’ll change the sheet names in the SyncPivotFields code:

Here is the section where you’ll change the pivot table names:

Download the Sample File

To download this version of the sample file, with Jeff’s code, please visit the Sample Files page on the Contextures website.

Note: Jeff’s sample file was updated on Sept. 20, 2012, so please download the new version if you have an older copy of the file.

In the Pivot Tables section, look for: PT0029 – Change Pivot Table Fields on Specific Sheets

The file is in Excel xlsm format, zipped, and contains macros. Enable the macros when opening the file, if you want to test the code.
__________________