This tutorial explains how to use Excel VBA to refresh all Pivot Table or Pivot Cache, and difference between PivotCache.Refresh and PivotTable.RefreshTable
You may also want to read:
Excel VBA refresh closed workbook
Excel automatically refresh pivot table
Excel VBA refresh all Pivot Table or Pivot Cache
Understanding difference between Pivot Table and Pivot Cache
To start with this topic, you should note the relationship and difference between refresh Pivot Table and Pivot Cache.
When you create a Pivot Table, Pivot Cache is created to store the source data in cache memory to speed up Pivot Table. Afterwards, Pivot Table will use the Pivot Cache as data source. If you are using multiple Pivot Tables using the same data source, all Pivot Tables use the same Pivot Cache in order to minimize your file size.
When you refresh a Pivot Table (right click on the Pivot Table > Refresh), you are refreshing Pivot Cache. Because Pivot Cache is refreshed, Pivot Table also gets refreshed. Therefore, you refresh all Pivot Tables that uses the same Pivot Cache by refreshing just one Pivot Table.
If you want Pivot Table to be independent, you can use Name Range to name the data source to other names (e.g Table1, Table2), and assign different name range to different Pivot Tables.
Read the below three VBA Methods regarding Refresh Pivot Table.
PivotTable.RefreshTable Method – Refreshes the PivotTable report from the source data. Returns True if it’s successful.
PivotTable.PivotCache().Refresh() Method – Updates the cache of the PivotTable object
Although VBA provides two different Methods, all of them will refresh Pivot Cache in the end, so it doesn’t matter which one to use. Therefore in the following sections, I will only show how to refresh Pivot Table.
Refresh all Pivot Table or Pivot Cache
To refresh all Pivot Table, there are two ways.
1) Using RefreshAll Method
The below action of VBA code is same as clicking Data > Refresh All
ActiveWorkbook.RefreshAll
This method is very simple and refreshes all Pivot Tables in the workbook, however, it also refreshes all other external connections.
2) Loop through individual Pivot Table and use RefreshTable Method
RefreshTable Method is same as right click on a Pivot Table and click “Refresh”. Although the below code looks like only refresh only one Pivot Table for each loop, but as I noted previously, refreshing one Pivot Table would refresh all other Pivot Tables using the same Pivot Cache.
Public Sub refreshPT()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next
Next
End Sub
Excel VBA refresh Pivot Table of specific name
The below code shows how to refresh Pivot Table of specific name (as noted above, this will also refresh Pivot Tables using the same Pivot Cache).
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
OR
ActiveSheet.PivotTables("PivotTable1").RefreshTable
You can also loop through each name in the workbook.
Public Sub refreshPivot()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
Select Case pt.Name
Case "PivotTable1", "PivotTable2"
pt.RefreshTable
End Select
Next
Next
End Sub