October 17, 2015
As far as I know you can filter multiple pivot tables with one slicer only if the pivot tables share the cache.
I have one big source of data which I have to group and distinct count by various fields, which I'm doing with separate queries in Power Query. Because of this, I ended up with separate pivot tables.
I believe that the only way I can filter these multiple pivot tables with one slicer is with a help of a macro, but I'm not VBA savvy at all.
I would be very grateful for ideas.
Thank you,
Blanka
January 18, 2015
Perhaps the following will help:
http://www.jkp-ads.com/Article.....cers04.asp
If you want to start learning VBA, have a look at the links in Post #9 in the following:
October 17, 2015
I adapted the macro from the first link posted by Derek and it works, just extremely slowly and freezes up the workbook. It synchronizes all the slicers/pivot tables in the workbook, but it takes a good minute or two each time a slicer button is clicked. I'm afraid my end users will not be happy.
Any ideas how to speed it up?
Thanks you so much, again.
January 18, 2015
October 17, 2015
Derek,
Much better. The wait time went down to about 15-20 seconds.
Does it matter exactly where the "Application.Calculation" lines go? Should the Manual go right after the variable declarations, and the Automatic one right before the End Sub?
I appreciate your help.
Blanka
January 18, 2015
In the jkp-ads example, there is a "Application.ScreenUpdating" line. I would put after it (at the beginning of the procedure) and before it at the end of the procedure. You can of course add code to do the recalculation should that be necessary.
I tend to declare all my variables first (alphabetically to make maintenance easier), then in most cases the "Application" code (e.g. ScreenUpdating, Calculation, EnableEvents) goes next. The order in which these appear depend on what I am trying to do. The "Application" code at the end of the procedure will be in reverse order to the way it was written at the beginning (e.g. EnableEvents, Calculation, ScreenUpdating).
Answers Post
January 18, 2015
It may not be relevant in this case but if the workbook is being opened on a network drive, that would increase the running time.
In my last job I created an automation process that I recommended was run on the C: drive. If run on a network drive the run times would increase by a factor of around 10.
January 18, 2015
Are you likely to have more than one workbook open at the same time?
If so, the following may be of interest:
https://msdn.microsoft.com/en-.....office.14).aspx
If the workbook has a lot of conditional formatting, that may affect calculation speed.
October 17, 2015
This made for interesting reading; thank you for the link.
Not much conditional formatting going on in my workbook, but I finally decided to go a different route. I created and merged more queries in Power Query, so now I'm able to have it all in one pivot table and same cache. It takes a longer time to update the workbook with the new data, but I can deal with this as it happens only once a month and I'm the only one affected. My users now are able to work with the dashboards at a quick pace.
Thank you for all your help.
January 18, 2015
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Blanka,
Glad to hear you managed to make it work.
Just a minor observation: Can you please mark the relevant answers in this topic? Each message has a "Useful Answer" link below the message text, to help other visitors identify more easily the solution they are hunting for, so it will be a great help for them.
Thanks
1 Guest(s)