VBA – Refresh Pivot Table / All Pivot Tables

This tutorial will demonstrate how to Refresh a Pivot Table or all Pivot tables using VBA.

Pivot tables are an exceptionally powerful data tool of Excel. Pivot tables enable us to analyze and interpret large amounts of data by grouping and summarizing fields and rows.

When we create a Pivot table, the data is stored in what is known as a Pivot table cache.

If the data source of the Pivot table is updated, the Pivot table itself does not get updated. The user in Excel needs to click on the Refresh All button in the Data tab on the Ribbon to refresh the source data of the Pivot Table.

Alternatively, we  can write macros in VBA which will update the data for us!

Refresh all Data Connections

The most simple method to refresh the data is to use VBA to mimic what the Refresh All button does on the Ribbon.

Sub RefreshConnections()
    ActiveWorkbook.RefreshAll
End Sub

This method will refresh all the connections to any data in the workbook that you have active.  It will also not just refresh a single Pivot table but will refresh multiple Pivot tables if you have more than one connected to different data sets.

Refresh all Pivot Tables

To refresh just the Pivot tables in our workbook but exclude any other data connections, we can use a method called RefreshTable.

If we have multiple Pivot tables in our workbook, we would need to loop through all of the Pivot tables in order to refresh all of them.  In order to do this, we first declare a Pivot Table Variable and then create a For Each Loop to loop through all the Pivot tables in the Active workbook.

Sub RefreshPivotsOnly()
  Dim tblPivot As PivotTable
  For Each tblPivot In ActiveWorkbook.PivotTables
     tblPivot.RefreshTable
  Next tblPivot
End Sub

We can use a similar macro to refresh the Pivot tables in our Active Sheet rather than the entire workbook. We would then loop through the Pivot tables in the ActiveSheet rather than the ActiveWorkbook.

Sub RefreshActiveSheetPivotsOnly()
   Dim tblPivot As PivotTable
   For Each tblPivot In ActiveSheet.PivotTables
      tblPivot.RefreshTable
   Next tblPivot
End Sub

This macro would be most useful if we had easy access to it on our ActiveSheet. In order to to this, we can create a button on the sheet to run the macro.

Refresh one Pivot Table

If we just want to refresh the Pivot table we are working on and not all the other Pivot tables in the workbook, we need to identify the specific Pivot table. This is of course as long as you know the name of the Pivot table – in this case PivotTable1.

Sub RefreshOneTable
   ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

Refreshing the Pivot Table Cache

If we have multiple Pivot tables in our workbook, but they all use the same data, we can refresh the Pivot table cache rather than refreshing the actual Pivot table. Refreshing the cache will then automatically refresh all the Pivot tables that are using the data contained in the cache.

Sub RefreshCache()
   Dim chPivot As PivotCache
   For Each chPivot In ActiveWorkbook.PivotCaches
       chPivot.Refresh
   Next chPivot
End Sub