How Do I Quickly Refresh Pivot Tables that have different Pivot Caches with VBA?

As I’ve mentioned before – if you’re working with a single dataset, the ideal scenario would be that your spreadsheet doesn’t contain pivot tables with different pivot caches.

The ideal scenario is not always possible, however. Sometimes you can’t avoid pivot tables that have different pivot caches as your analysis might need to draw on information that comes from different datasets. Whether the data comes from different tables in a single database or different databases altogether, it will always result in two different pivot caches (unless you do some data prep in PowerPivot or in a separate file where you merge all the datasets you need)

How do you refresh then?

You’ll need a loop in any case.

One way would be to simply loop through the different pivot caches in your workbook and refresh these.

The second option would loop through all the pivot tables and refresh each one of them.

Even though both approaches will ultimately produce the same end result, one might be preferable over the other depending on your spreadsheet setup.

For example – if your spreadsheet contains a couple of tables that share the same pivot cache, it might be more efficient to refresh the caches instead of looping through the pivot tables. This way you will not be refreshing the same pivot cache over and over again.

Sub RefreshPTs()
Dim Wbk As Workbook
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PC As PivotCache
'written by Angelina Teneva
'refresh pivot caches
'useful when you have multiple caches '+ listobjects fed through SQL queries
'and you only want to refresh the pivot tables but not the listobjects
For Each PC In ActiveWorkbook.PivotCaches
Next PC
'refresh all pivot tables in a workbook
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In ActiveSheet.PivotTables
PT.SaveData = True
Next PT
Next Wks
End Sub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.