VBA – Refresh Pivot Table / All Pivot Tables

This tutorial demonstrates how to refresh a pivot table or all pivot tables using VBA.

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

When you 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 the Refresh All button in the Data tab on the Ribbon to refresh the source data of the pivot table.

Alternatively, you can write macros in VBA that update the data for you!

Refresh All Data Connections

The simplest 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 refreshes all the connections to any data in your active workbook. It refreshes multiple pivot tables if you have more than one connected to different data sets, not just the pivot table (or tables) based on a single source.

Refresh All Pivot Tables

To refresh just the pivot tables in your workbook but exclude any other data connections, use a method called RefreshTable.

If you have multiple pivot tables in your workbook, you need to loop through all of the pivot tables and refresh each of them. 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

A similar macro can refresh the pivot tables in the Active Sheet rather than the entire workbook. It should 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 with easy access from your ActiveSheet. Create a button on the sheet to run the macro.

Refresh One Pivot Table

To refresh just the pivot table you’re working on, and not all the other pivot tables in the workbook, you must identify the specific pivot table. This is, of course, if you know the name of the pivot table: here, PivotTable1.

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

Refresh the Pivot Table Cache

If you have multiple pivot tables in a workbook but they all use the same data, you can refresh the pivot table cache rather than refreshing an actual pivot table. Refreshing the cache automatically refreshes all pivot tables that use the cached data.

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!