VBA – Refresh Pivot Table / All Pivot Tables
In this Article
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
VBA Coding Made EasyStop 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!