Table of Contents
Refreshing pivot tables in VBA can be done with the PivotTable.RefreshTable method. This method is used to update the data in the source range for the pivot table. It takes no arguments and is called on the range object for the pivot table. For example, if the pivot table is in cell A1, the code to refresh it would be Range(“A1”).PivotTable.RefreshTable. By combining this method with other VBA commands, one can create useful macros to automate the refreshing of pivot tables.
You can use the following methods to refresh pivot tables in Excel using VBA:
Method 1: Refresh One Specific Pivot Table
Sub RefreshPivotTable()
Sheet1.PivotTables("PivotTable1").Refreshtable
End Sub
This particular macro will refresh only the values in the pivot table called PivotTable1 in Sheet1 of the workbook.
Method 2: Refresh All Pivot Tables in Workbook
Sub RefreshAllPivotTables()
ThisWorkbook.RefreshAll
End Sub
This particular macro will refresh the values in every pivot table in the entire workbook.
The following examples show how to use each of these methods in practice.
Example 1: Refresh One Specific Pivot Table
Suppose we have created one pivot table from a dataset in Excel:
When we are on the PivotTable Analyze tab along the top ribbon in Excel, we can see in the top left corner that this pivot table is named PivotTable1.
Suppose we change the last value in the points column of the dataset from 22 to 200:
We can create the following macro to refresh the values in this specific pivot table:
Sub RefreshPivotTable()
Sheet1.PivotTables("PivotTable1").Refreshtable
End Sub
When we run this macro, the values in the pivot table are automatically updated:
Example 2: Refresh All Pivot Tables in Workbook
Suppose we have created two pivot tables from a dataset in Excel:
The first pivot table shows the sum of points by team and position.
The second pivot table shows the average of points by team and position.
Suppose we change the last value in the points column of the dataset from 22 to 200.
We can create the following macro to refresh the values in all pivot tables in the entire workbook:
Sub RefreshAllPivotTables()
ThisWorkbook.RefreshAll
End Sub
When we run this macro, the values in both pivot tables are automatically updated:
