Excel VBA refresh all Pivot Table or Pivot Cache

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

Leave a Reply

Your email address will not be published. Required fields are marked *