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:
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Sum Values in Range
VBA: How to Calculate Average Value of Range
VBA: How to Count Unique Values in Range
