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

Comments on this post

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

Requesting Gravatar...
Hi,

The sample as published is no working in Excel 2000 / 2003 Dutch. The script as published in Google is working for me.

Maybe you can test something ?

Regards, Marco
Left by Marco Groeneveld on Dec 17, 2005 5:10 AM

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

Requesting Gravatar...
Hi,

I put two PivotTables (connected to one single OfflineCube) on a sheet. Problem was: If You select multiple childs (such like 2 or more years in the year-dimension), the code didn't synchronise.

I added 3 lines of code to solve this problem:

If Not pt.PageFields(pf.Name) Is Nothing Then
pt.CubeFields(pf.Name).EnableMultiplePageItems = True
pf.CubeField.EnableMultiplePageItems = True
pt.PageFields(pf.Name).CurrentPageList = pf.CurrentPageList
pt.PageFields(pf.Name).CurrentPageName = pf.CurrentPageName
End If
Left by Bernhard Saemmer on Feb 23, 2006 8:11 PM

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

Requesting Gravatar...
Hi Darren,

thanks a lot for making this update on my suggestion or proposal. You helped me very much with your example and my problem was not solved within 1 hour, because i am new to programming pivottables. debugging around and looking on the values made me to my additional 3 lines of code. If I have time, I will recode to Your IF-statements, but there is no time for me now and never touch a running code. :-)
I have to thank You! Be sure Your code will run in a governmental institution (State Ministry of Economic Affairs and Employment) in Germany. Thanks a lot and greetings from Germany

Bernhard
Left by Bernhard Saemmer on Mar 01, 2006 3:37 AM

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

Requesting Gravatar...
I literally tore all the hair off my head trying to achieve the same results but your code worked like a charm. All I had to do was just copy the code. Thank you very much. You have been of great help.
Left by Aditya Gollapudi on Mar 13, 2008 6:11 AM

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

Requesting Gravatar...
Hello Darren,

I found your code to synchronize the filtering of multiple pivottables. The code is working correct only if I select one item from the list, when I enable the "select multiple items" option and select one or more items then the second pivottable doesn't follow the filtering.

I tried this on a English and a dutch version of Excel 2007 Proffesional Plus.
Do you have a suggestion to solve this problem?

Regards,
André
Left by Andre on Apr 02, 2008 4:36 PM

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

Requesting Gravatar...
Are you sure you are using the version 1.4 download from the bottom link? I added the multi-select feature back in v1.1 and it works fine on my machine. I did a quick check through the code and I can see anywhere where the language should make a difference (there is just a different property I use to get the collection of filter members)
Left by Darren Gosbell on Apr 04, 2008 6:12 AM

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

Requesting Gravatar...
Hello Darren, I have found a the reason that the code isn't working. I did a test, I made a new workbook and added the code. I saved this workbook (my default is excel 2003) as a .xls file. I tested the code and the code was working (on the same source cube). After that I saved the file to a .xslm file (2007 macro enabled) and closed the file. After reopening this xlsm workbook the code didn't work when I selected multiple items. When I don't use the "select multiple items" option the code is still working.
I could not get the code working after i saved the file again back to a.xls file.
Do you have any suggestion to get this code working on the xlsm file.
I would thank you that you gave a reaction on my first comment.

Regards Andre
Left by Andre on Apr 05, 2008 11:35 PM

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

Requesting Gravatar...
Hello Darren,
Thank you very much for this code, I only wish I had seen it some before.
However, I have encountered an issue when pulling data from two different tables. The pivots from the second table appear to be updated (i.e. the page filter changes according to any changes), but the data pulled in is for another selection in the filter. The two tables have the same range of variables in the common field. It works well for a few times, but suddenly starts being incorrect, with no obvious pattern..
I am using Excel 2003, and your version 1.4.
Not sure if you are still supporting this one, but would highly appreciate if you have any thougts on this matter.

Thanks and regards,
Vidar
Left by Vegard on Aug 28, 2008 7:44 PM

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

Requesting Gravatar...
Hi Vidar,

I am happy to still support this code, but without being able to reproduce your issue I don't know what I can do. It could be something relating to the structure of your cube. Are you sure that it is not something like attribute overwritting influencing this?
Left by Darren Gosbell on Aug 28, 2008 10:48 PM

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

Requesting Gravatar...
Hi Darren, thank you for the quick reply. I thought I had solved the problem; that the issue was just that the pivots were set up to 'physically' overlap each other, but this turned out to not be the case. The pivots based on the external source (an Access query) seem to work fine, whereas some of the pivots based on a sheet in the Excel file removes options from the page filter.
I tried to remove the error handler, and it then stops at
If Target.CubeFields(pagefld.Name).EnableMultiplePageItems = True Then
Any inputs are highly appreciated.
Best regards,
Vidar
Left by Vegard on Sep 03, 2008 12:21 AM

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

Requesting Gravatar...
Actually, I have never tried using this macro against any data source other than Analysis Services. I really built it only with Analysis Services in mind. I'm not really sure how other data sources format their filters.
Left by Darren Gosbell on Sep 03, 2008 8:13 AM

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

Requesting Gravatar...
Hallo Darren,
you created a nearly perfect solution for my problems in Excel 2007. But i have also the problem that the code isn´t working with "multiple choice". My pivot tables are based on a cube created with analysis services. When activating the checkbox "more than one element" in the reporting filter the makro does not work. Do you have any idea...
Left by Carsten on Sep 17, 2008 12:08 AM

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

Requesting Gravatar...
Hi Carsten,

Are you using the latest version? In the demo workbook, I have an example with multiple filters that is working fine.

The code should work. Are you able to reproduce this issue against Adventure Works? If you can provide steps to reproduce this issue I can see if I can figure out what is going on.
Left by Darren Gosbell on Sep 17, 2008 1:43 PM

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

Requesting Gravatar...
Hi Mike

Yeah, I have found the same thing. I know that when you save a file as an xls that the pivot table falls back to using a backward compatibility mode, so even though they look the same, under the covers it is using different code.

I have not yet figured out if it is possible to get this code working with an xlsm file, but I have not really sat down and had a concerted go at it. Recording a Macro I came up with the following which references the VisibleItemsList, so I may have a go at seeing if I can use that.

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Date].[Calendar].[Month]").VisibleItemsList _
= Array("[Date].[Calendar].[Month].&[2004]&[1]", "[Date].[Calendar].[Month].&[2004]&[2]")

Cheers
Darren

Left by Darren Gosbell on Dec 10, 2008 3:23 PM

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

Requesting Gravatar...
Hi Darren, I think those links to the files don't work anymore. Do you have a new downloading section?
BR Fabian.
Left by Fabian on Feb 27, 2009 4:11 PM

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

Requesting Gravatar...
Hi BR Fabian, It appears that then entire site that was hosting these files is no longer available. So I have change the link to the latest release to my skydrive.
Left by Darren Gosbell on Feb 28, 2009 12:16 PM

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

Requesting Gravatar...
This sample is very useful.

By the way, I've found some interesting behavior from my tests. I wonder there are some issues in case of PivotTable version 3.

In excel 2003 compatible mode, pivotTble.Version returns 1, pagefld.Name returns 2-parts name, and
EnableMultiplePageItems works well.

However, in non-compatible mode, pivotTble.Version returns 3, pagefld.Name returns 3-parts name, and
EnableMultiplePageItems does not work well.

Is the any good information on this?
Left by Ohoo Kwon on Apr 27, 2009 9:30 PM

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

Requesting Gravatar...
Sorry but I have not found any good information on programming against a pivottable in the different compatibility modes. There are a few things in my sample code that don't work in Excel 2007 in non-compatibility mode and I have not been able to find any information that would allow me to fix them.
Left by Darren Gosbell on Apr 28, 2009 7:23 AM

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

Requesting Gravatar...
Firstly Darren,

Thank you for your time in developing something I have been dredging the net for! I hope Microsoft add this feature to their next version!

I, however, cannot get the code working for multiple selctions in a Page field (I am using 2007). I have downloaded the version that you had above. I know I am repeating the same concern as some other readers, but hope that you have revised and improved your code to clear this hurdle.

Kind regards
Dave
Left by David Williamson on Sep 04, 2009 8:04 AM

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

Requesting Gravatar...
Hi Dave,

Sorry, but I have not had much success getting this code to work in Excel 2007. In Excel 2007 they enhanced the way pivot tables work when connected to SSAS and I have yet to find a way to keep the page filters synch'ed in the 2007 mode. The properties that I used to set in Excel 2003 do not appear to work the same in 2007.

Darren
Left by Darren Gosbell on Sep 04, 2009 9:07 AM

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

Requesting Gravatar...
Hi,

I tried your code and it worked like a charm. I'm still trying to deal with the multiple selections but oh well.

My question, however, is on the trigger. The code watches for a change to a pivot and then updates the rest of the pivots. I have a combo box that updates one of the pivots. I was hoping that updating that would trigger the event, but it hasn't. I'm new enough to VB that I'm not sure how to go about modifying the code to recognize any updates to the pivot, not just a direct one. Or is there a way I can call the class module directly without waiting for the event trigger?

Thanks for your help.
Left by Catherine on Oct 14, 2009 5:41 AM

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

Requesting Gravatar...
@Catherine Yes, you could call into my code manually if it is not automatically detecting changes. You would just have to call the UpdateAllPivotTables method passing in PivotTable object that your code just modified.
Left by Darren Gosbell on Oct 14, 2009 5:59 AM

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

Requesting Gravatar...
Is there any chance you could fix multiple-selection to work for non-compatibility mode Excel2007+?

In compatibility mode it returns [Customer].[City] and CubeFields works, in non-compatibility it returns [Customer].[City].[City] and call to CubeFields fails..
Left by Algirdas on Nov 14, 2010 10:13 AM

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

Requesting Gravatar...
I found a way to fix it! Will post tomorrow - 2am now..
Left by Algirdas on Nov 14, 2010 11:06 AM

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

Requesting Gravatar...
ok, so anyway, here it is:



If Not pivotTbl.PageFields(pagefld.Name) Is Nothing Then
'MsgBox (pagefld.VisibleItemsList)
If pagefld.CubeField.EnableMultiplePageItems = True Then
pivotTbl.PageFields(pagefld.Name).CubeField.EnableMultiplePageItems = True
pivotTbl.PageFields(pagefld.Name).VisibleItemsList = pagefld.VisibleItemsList
Else
pivotTbl.PageFields(pagefld.Name).CubeField.EnableMultiplePageItems = True
pivotTbl.PageFields(pagefld.Name).CurrentPageName = pagefld.CurrentPageName
End If
Left by Algirdas on Nov 18, 2010 7:40 AM

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

Requesting Gravatar...
Thank you very much for that great piece of code you produced. Unfortunately I'm experiencing the same problem with Multiple Selection as described in many other posts. Tried updating the Macro with code from Algirdas posted on 11/18/2010 but unfortunately it did not help.

Does anyone have a solution?
Left by OlegK on Jul 27, 2011 8:21 PM

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

Requesting Gravatar...
@OlegK - did you try the SynchronisedPageFieldsCrossSheet2007_v2.xlsm file in the skydrive folder? That works for me with multiple selections.
Left by Darren Gosbell on Aug 01, 2011 10:59 PM

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

Requesting Gravatar...
How do I access the skydrive?
Left by Dieter on Dec 01, 2011 6:49 AM

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

Requesting Gravatar...
@Dieter - you click on the link to the zip file at the bottom of the post. (it has a zip file icon next to it). Then you should be able to right click on the file or click on the download link.
Left by Darren Gosbell on Dec 01, 2011 9:59 AM

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

Requesting Gravatar...
Hi Darren,

Not sure if I am missing a point here...but I am unable to see any link to your skydrive here. Would it be possible to send me the same.

Regards,
Narayanan.
Left by Narayanan Alaghappan on Jan 03, 2012 12:11 AM

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

Requesting Gravatar...
It's right at the bottom of the post. Here it is again https://skydrive.live.com/self.aspx/Public/Blog/Excel%20Pivot%20Table%20Synch/SynchronisedPageFieldsCrossSheet%5E_1%5E_4.zip?cid=98546e1b65a78a74&sc=documents
Left by Darren Gosbell on Jan 03, 2012 6:50 AM

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

Requesting Gravatar...
Just want to say a big thank you, I have been looking for something like this that works with Analysis Services Cubes for a long time and it works a treat!

Cheers mate!

Left by Hamish on Mar 09, 2015 7:59 AM

Your comment:

 (will show your gravatar)