An Excel Workbook that keeps the filters for all pivot tables synchronised

I have been answering a couple of questions on the microsoft.public.sqlserver.olap newsgroup about code for keeping the page filters for multiple pivot tables in synch in the one Excel workbook. One person had 2 pivot tables on the one sheet and another had 2 sheets with a pivot table on each one. I was starting to get mixed up between the two threads so I thought I would just build a solution that would work across all pivot tables across all sheets in the workbook.

The workbook I created contains a small bit of startup code in the workbook_open event, but apart from that all the code is contained in a class module. There is no need to change anything as worksheets are added or delete from the workbook, the class should take care of everything.

I have created a project on ProjectDistributor.net here, just click on the link for the sourcecode, if you have any feedback you can either leave it on the projectDistributor site or on my blog. If you just want the file you can get it directly from this link (now superceded by v1.1 below)

Updated: 24 Feb 2006 Added new functionality to allow for multi-member selections in the filter list.. I have taken the code suggested by Bernard in the comments and extended it a little. Thanks Bernard.

Updated: 20 May 2006 Added new functionality to work with pivot tables based on relational sources.

Updated: 23 May 2006 Added new functionality so that the macro will synchronise a full sheet pivot chart. Also corrected and issue where the code would crash when the workbook contained a standard full sheet chart. The automatic column sizing will only kick in now if one the pivot tables on the current sheet has it's “Auto Format” property turned on. So, if you don't want the auto sizing columns, turn off the Auto Format setting under the Table Options for the pivot tables. 

If anyone has workbooks using earlier versions of this code you can upgrade by copying the PivotTableUpdater class from the latest version - all of the changes are contained within this class, the basic “glue” code that instantiates this class has not altered since version 1.0.

Updated: 13 June 2006 Disable the cancel key while all the pivottables are being synchronised to prevent the users canceling the macro half way through an update.

Updated: 28 Feb 2009 Apparently the ProjectDistributor website has gone off the air, so I have removed those  broken links and put the latest release up on my skydrive.

Updated 1 Aug 2011 Added a version for 2007/2010 pivot tables (xlsm file)

Print | posted on Wednesday, December 14, 2005 4:43 PM