Which way is better to refresh pivot tables

[mark hansen]

I've been looking around the interweb for way to use VBA to refresh pivot tables and discovered there is also a pivot cache refresh. I found the following, and was wondering is one way better (maybe faster) than the other...

Set WS = ActiveSheet
For Each PT In WS.PivotTables
PT.PivotCache.Refresh
Next PT

Or
Set WS = ActiveSheet
For Each PT In WS.PivotTables
PT.RefreshTable
Next PT


What is the difference in data quality and or speed?
Thanks,
Mark

 

[6StringJazzer]

Neither one is "better." As with most things, it depends on what you're doing.

Multiple pivot tables may share the same cache if they are using the same data source, so updating the cache may affect multiple tables. Updating a table will update just that table. There may also be differences depending on whether you have selected the option to store the data with the workbook or not, but I have not explored this. But there is no difference in data quality, and I'm not even sure what you mean by that.

(Please use code tags around code.)

 

[mark hansen]

Thanks... I think I need to look at one particular sheet that takes 7 seconds to finish all of the refreshing. All of the pivot tables and charts come from one table and there are 8 charts, and 13 tables and 9 slicers.

I appreciate you help.
Mark