This code will refresh all the Pivot Tables of the active workbook.
Dim Table As PivotTable
For Each Table In ActiveWorkbook.PivotTables
Table.RefreshTable
Next Table
A Pivot Table is a special type of table in an Excel worksheet that contains two categories of data titled Row and Value, respectively. All the columns of the table are categorized under these two categories.
It’s generated automatically from a data set if you select the data set and go to the PivotTable option in the Insert toolbar.
Method 1 – Refreshing a Single Pivot Table in Excel
To refresh a single Pivot Table, first, you have to refer to it as a PivotTable object. Then you have to use the RefreshTable method of VBA. We’ve got a Pivot Table called PivotTable1 in the active worksheet.
- Use the following lines of code:
Dim Table1 As PivotTable
Set Table1 = ActiveSheet.PivotTables("PivotTable1")
Table1.RefreshTable
- Run the code, and it’ll refresh the Pivot Table called PivotTable1 in the active worksheet.
Read more: How to Auto Refresh Pivot Table in Excel
Method 2 – Refreshing All Pivot Tables of a Worksheet in Excel
To refresh all the Pivot Tables of the active worksheet, you have to iterate through each Pivot Table of the ActiveSheet.PivotTables object. Then use the RefreshTable method.
Dim Table As PivotTable
For Each Table In ActiveSheet.PivotTables
Table.RefreshTable
Next Table
Read more: Pivot Table Not Refreshing
Method 3 – Refreshing All of the Pivot Tables of a Workbook in Excel
To refresh all the Pivot Tables of the active workbook, iterate through each table by the ActiveWorkbook.PivotTables object of VBA. Then use the RefreshTable method.
Dim Table As PivotTable
For Each Table In ActiveWorkbook.PivotTables
Table.RefreshTable
Next Table
Read more: How to Automatically Update a Pivot Table When Source Data Changes
Method 4 – Refreshing the Pivot Table Cache with Excel VBA
To refresh the Pivot Table cache, iterate through each Pivot Table cache of the active workbook by the ActiveWorkbook.PivotCaches object.
Dim Cache As PivotCache
For Each Cache In ActiveWorkbook.PivotCaches
Cache.Refresh
Next Cache
Read more: How to Auto Refresh Pivot Table without VBA in Excel
Download the Practice Workbook
