How to Refresh All Pivot Tables with VBA (4 Ways)

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.


Refresh All Pivot Tables with VBA in Excel (Quick View)

Dim Table As PivotTable
For Each Table In ActiveWorkbook.PivotTables
    Table.RefreshTable
Next Table

This will refresh all the Pivot Tables of the active workbook.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Ways to Refresh All Pivot Tables with VBA in Excel

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.

Pivot Table to Refresh All Pivot Tables with VBA in Excel

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.

Pivot Table with Name to Refresh All Pivot Tables with VBA in Excel

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. Refresh 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. (Click here to know how to get the name of Pivot Table).

Pivot Table to Refresh All Pivot Tables with VBA in Excel

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 Methods)


2. Refresh All the 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

VBA Code to Refresh All Pivot Tables with VBA in Excel

Run the code, and it’ll refresh all the Pivot Tables of the active worksheet.

Read more: Pivot Table Not Refreshing (5 Issues & Solutions)


3. Refresh All 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. Refresh the Pivot Table Cache with VBA in Excel

If you have multiple Pivot Tables in your workbook that use the same data, you better refresh the Pivot Table cache rather than refreshing 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 (3 Smart Methods)


Things to Remember

Here we’ve shown 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.


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.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo