Perhaps the following will help:

http://www.jkp-ads.com/Articles/slicers04.asp

If you want to start learning VBA, have a look at the links in Post #9 in the following:

http://www.mrexcel.com/forum/excel-questions/910633-learning-visual-basic-applications.html

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.

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.

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.

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

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).

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.

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.

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.

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.

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.

Great news, Blanka. Glad you have a workable solution and even better that Power Query was useful.

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!

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