Register
Log In
Unanswered
Home
Excel Analysis Toolpak
October 17, 2015
Offline
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
See AnswerJanuary 18, 2015
Offline
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:
Excel Analysis Toolpak
October 17, 2015
Offline
Thank you, Derek.
I was hoping it would be easier and simpler. I started reading the first article and my head is spinning. I'll have to try and play around with it.
Thank you again.
Excel Analysis Toolpak
October 17, 2015
Offline
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
Offline
My first suspicion would be Excel calculation taking place (probably main times as changes are made).
Try:
Application.Calculation = xlCalculationManual
at the beginning of the code and
Application.Calculation = xlCalculationAutomatic
at the end.
Excel Analysis Toolpak
October 17, 2015
Offline
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
Offline
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
Excel Analysis Toolpak
October 17, 2015
Offline
Many thanks, Derek.
I did as instructed by you and now the wait time went down to about 8-10 seconds on average. Not ideal, but my end users may have to learn to live with this.
Thank you again.
January 18, 2015
Offline
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.
Excel Analysis Toolpak
October 17, 2015
Offline
Yes, unfortunately the file must reside on the network. Recently we moved to Virtual Desktop and nobody in the office has a real C: drive as far as I know.
January 18, 2015
Offline
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-us/library/office/ff700515(v=office.14).aspx
If the workbook has a lot of conditional formatting, that may affect calculation speed.
Excel Analysis Toolpak
October 17, 2015
Offline
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.
July 16, 2010
Offline
Great news, Blanka. Glad you have a workable solution and even better that Power Query was useful.
January 18, 2015
Offline
Thank you very much for the feedback.
I am very pleased that you have been able to get a good solution to the problem. Well done!
November 8, 2013
Offline
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
Most Users Ever Online: 57
Currently Online: Ali Bokhari
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ali raza
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
