Speeding Up Pivot Table Reporting Using Macros

[SpikeGiles]

I have a large data set with many products and product codes that I use to generate several reports, each reporting on a separate group of codes. I set up macros to select the codes, or, more precisely, the VBA behind the macros indicates which codes should not be visible, for each report. The problem is that when the macro is run it seems that the pivot table is updated each time one code is evaluated. There are 40 codes in all, so the macro takes several minutes to run. Is there a way to suppress the constant updating of the pivots so that all code selections can be done and then update the pivot table at the end?

 

[Fazza]

hi. please try via the 'ManualUpdate' of the pivot table

https://msdn.microsoft.com/en-us/VB...tFrameworkMoniker-Office.Version=v15)&rd=true

so set to manual update, do what you need to do, and then turn manual update off

cheers

 

[SpikeGiles]

Thanks Fazza, I will be trying this out today and will post the results.