Deactive Pivotupdate while updating Filter VBA

Hey guys,

So I am working on creating a dashboard in excel, using several different pivottables with external sources.
Currently I am trying to create an update buttom that updates all the external pivottables (which have been named for simplicity) and then updating the pivot's on those pivots after.

During this update I want my filter on the external PivotTables to be updated according to todays date. It works like this: If we take today as an example, we are in 2017, quarter 3, month 9, week 39. Since the database is giving me numbers that are not comparable for the week we are in, I only need this years date selected.
I have written this in the Pivot and the filtering itself works fine.

My problem occurs during this filtering process, as the OLAP query runs several time making the update process EXTREMELY slow. When I select the filter from clicking on the Pivot Table and selecting the weeks to be included it only runs the OLAP query once, and thus it only takes around 15-20 seconds. But when I do (exactly) the same with my VBA macro it runs the OLAP query several times, and the update takes more than a minute. Having to do this for 6 pivots takes up too much time.

Is there anyway I can make it stop updating the pivot untill the filter has been selected, or in any other way stop it from running the OLAP query several times?

Here is the code I have made. Be aware, it is the first thing I've done, so some of it could probably have done simpler:

Please Login or Register  to view this content.

It is the last part that does the filtering. It does it the same way as when I recorded the macro from doing it myself, hence, it shouldnt take longer than during it manually.

This is part of a longer code that creates the arrays from today's date, and that itself works perfect. The thing that slows it down is this pivotFields(...).VisibleitemsList that creates several OLAP query.

Any ideas on how to speed this filtering up?

Best
Anders

Hi xlnitwit,

Sorry for the slow reply.
It appears as though your suggested command was enough to delay the update. Much appreciated!

Have a great day.

Best,
Anders