In this article, I’ll show you how you can refresh all the Pivot Tables of your worksheet or workbook with VBA in Excel. You’ll also learn to refresh a single Pivot Table, as well as the Pivot Table cache.
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 Insert > PivotTable option in the Excel toolbar.
Today our objective is to learn how we can refresh all the Pivot Tables of a worksheet or a workbook with VBA in Excel.
1. Refreshing a Single Pivot Table in Excel
First, we’ll learn to refresh a single Pivot Table.
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.
Here we’ve got a Pivot Table called PivotTable1 in the active worksheet.
To refresh it, we have to 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
2. Refreshing All Pivot Tables of a Worksheet in Excel
Now we’ll refresh all the Pivot Tables of a worksheet with VBA.
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
Run the code, and it’ll refresh all the Pivot Tables of the active worksheet.
Read more: Pivot Table Not Refreshing
3. Refreshing All of the Pivot Tables of a Workbook in Excel
You can also refresh all 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
This code will refresh all the Pivot Tables of the active workbook.
Read more: How to Automatically Update a Pivot Table When Source Data Changes
4. Refreshing the Pivot Table Cache with Excel VBA
If you have multiple Pivot Tables in your workbook that use the same data, you better refresh the Pivot Table cache rather than refresh the Pivot Table.
To refresh the Pivot Table cache, iterate through each Pivot Table cache of the active workbook by the ActiveWorkbook.PivotCaches object.
Then use the Refresh method of VBA.
Dim Cache As PivotCache
For Each Cache In ActiveWorkbook.PivotCaches
   Cache.Refresh
Next Cache
It will refresh all Pivot Table cache from the active workbook.
Read more: How to Auto Refresh Pivot Table without VBA in Excel
Things to Remember
Here we’ve shown how to refresh the Pivot Tables from the active worksheet or workbook only. If you want to refresh data from a workbook or worksheet that’s not active, use the name of the worksheet or workbook in place of the ActiveSheet or the ActiveWorkbook object.
For example, to refresh all Pivot Tables from Sheet1, use For Each Table In Worksheets(“Sheet1”).PivotTables.
And to refresh from Workbook1, use For Each Table In Workbooks(“Workbook1”).PivotTables.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Using these methods, you can refresh all the Pivot Tables from a worksheet or workbook with VBA in Excel. Do you have any questions? Feel free to ask us.
