VBA Refresh Pivot Table

What is Excel VBA Refresh Pivot Table?

In Excel, Pivot Tables are powerful tools for summarizing and analyzing data. They allow you to create dynamic reports and charts based on your dataset. However, the data in a Pivot Table may change over time, and you’ll need to update it to reflect the latest information. Excel provides a manual option to refresh Pivot Tables, but you can also automate this process using VBA Refresh Pivot Table.

VBA Refresh Pivot Table means writing a custom script that triggers the refresh operation. VBA is a programming language that allows you to automate tasks and interact with Excel’s objects and features. By writing VBA code, you can create macros to VBA refresh Pivot Tables automatically, saving you time and ensuring your reports are always up to date.

Let’s say you have a dataset and a Pivot Table based on that data. Initially, your Pivot Table accurately summarizes the data:

However, if you make changes to your source data, such as updating a cell value:

Even though you’ve made changes to the source data, the Pivot Table doesn’t automatically reflect those changes:

To resolve this problem, you must manually click the “Refresh” option under the “PivotTable Analyze” menu.

This action ensures that your Pivot Table updates and accurately displays the revised values.

However, you can create a simple Excel macro using the VBA Refresh Pivot Table whenever there’s any change in the source data. This automation ensures that your Pivot Table always provides the most current information.

By implementing this solution, you eliminate the need for manual updates and ensure the accuracy of your reports, even as your data evolves.

In the upcoming section, we will explore automating the refresh of Pivot Tables.

How to Auto Refresh Pivot Table Data VBA Code?

#1 – Simple Macro to Refresh All Pivot Tables

Suppose you have a pivot table, and you want to automate the VBA refresh pivot table when data changes. We can use the following procedure:

• Step 1: Change Event of the Datasheet

The code is enclosed within a Worksheet Change event. This event is triggered automatically when any changes occur within the cells of the worksheet where the code is placed. In this context, it’s used to detect any changes in the worksheet and respond accordingly.

• Step 2: Use Worksheet Object

Here, we declare a variable named “ws” as a “Worksheet” object.

The purpose of the worksheet object “ws” is to point to the worksheet containing the pivot table. The workbook containing the code is represented by “ThisWorkbook.Sheets(“Sheet2″),” and the worksheet named “Sheet2” is specified by “Sheets(“Sheet2″).”

• Step 3: Refer Pivot Table by Name

The code then uses “ws.PivotTables(“PivotTable1″)” to access a particular “Pivot Table” inside the designated worksheet, specifically naming it “PivotTable1.”

The data in a Pivot Table’s cache is refreshed using “.PivotCache.Refresh”.

• Step 4: Use the Refresh Table Method

The code concludes with the invocation of the “PivotCache.Refresh” method. This method is applied directly to the Pivot Table’s cache, which updates the Pivot Table to reflect any changes in the underlying data source.

Additionally, the code employs error handling with On Error Resume Next to gracefully manage situations where the specified Pivot Table or worksheet does not exist. If an error occurs, it continues to the next line of code (On Error GoTo 0) without raising an error message.

• Step 5: Now, save the Macro. This macro attempts to refresh a specific Pivot Table (“PivotTable1”) located in “Sheet2” whenever a change occurs in the worksheet where it’s placed.

For example, in Sheet1, if we change the value of Cell E2 to 500, it automatically refreshes the pivot table to give the accurate number.

Here is the complete code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet2”)
ws.PivotTables(“PivotTable1”).PivotCache.Refresh
On Error GoTo 0
End Sub

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 – Refresh All Pivot Tables of the Same Worksheet

To refresh all Pivot Tables on a single worksheet, you can use the following VBA code:

This code defines a subroutine that iterates through all Pivot Tables on the specified worksheet and refreshes each one.

• Step 1: In the new module, start by creating a subroutine, Sub RefreshAllPivotTables(), and declare two variables, “ws” and “pt.”

• Step 2: In this step, you need to identify the worksheet containing the Pivot Tables you want to refresh. In this example, it will be Sheet3.

• Step 3: Inside the VBA code, we start by declaring a variable ‘ws’ to represent the target worksheet. We then use a For Each loop to iterate through all the Pivot Tables within that worksheet.

• Step 4: Within the loop, we use the pt.RefreshTable method to refresh each Pivot Table individually. It ensures that all Pivot Tables on the specified worksheet are refreshed.

• Step 5: Now, save the macro and exit the VBE.

Now, make the changes you wish to the data and then click on Alt + F8 to open the macro window.

In the macro window, select “RefreshAllPivotTables” and click on Run.

When you run the code, it refreshes all Pivot Tables in the “Sheet3” worksheet, ensuring that they display the latest data from their respective sources.

Here is the full code:

Sub RefreshAllPivotTables()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet3”)
Dim pt As PivotTable
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
End Sub

#3 – Refresh All Tables in the Workbook

To VBA refresh Pivot Tables in the entire workbook, you can use the following code:

This code iterates through all worksheets in the workbook and, for each worksheet, iterates through all Pivot Tables and refreshes them.

• Step 1: Just like in the previous example, the code starts by declaring two variables: ws (for worksheets) and pt (for Pivot Tables).

• Step 2: The key difference in this code compared to the previous example is that it iterates through all worksheets in the workbook using the “For Each ws In ThisWorkbook.Sheets” statement, whereas the previous example specifically targeted the “Sheet3” worksheet.

• Step 3: Similar to the previous example, within the worksheet loop, another loop is initiated to iterate through all the Pivot Tables within the current worksheet using the For Each pt In ws.PivotTables statement.

• Step 4: Just like in the previous example, inside the Pivot Table loop, the pt.RefreshTable method is called on each Pivot Table. This action refreshes the data in each Pivot Table, ensuring they display the latest information from their respective data sources.

• Step 5: After refreshing the Pivot Table within the current worksheet, the code continues iterating through other worksheets (if any) and their respective Pivot Tables, which is also similar to the previous example.

• Step 6: Save the macro and click on run.

When you run code, it iterates through all worksheets in the workbook (ThisWorkbook.Sheets), and within each worksheet, it iterates through all Pivot Tables (ws.PivotTables). For each Pivot Table encountered, the code refreshes it using the pt.RefreshTable method.

Here is the full code:

Sub RefreshAllPivotTablesInWorkbook()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Sheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub

#4 – Avoid Loading Time by Using Worksheet Deactivate Event

You can also use the Worksheet Deactivate event to refresh Pivot Tables when you switch to another worksheet automatically. This can help avoid loading time when you open the workbook. Here’s an example of VBA code to achieve this:

This code should be placed in the code window of the worksheet where you want the Pivot Tables to refresh when you deactivate it.

• Step 1: The code starts by declaring a variable pt of type PivotTable. This variable will be used to represent each Pivot Table within the worksheet.

• Step 2: Inside the Worksheet_Deactivate event, a For Each loop is initiated to iterate through all Pivot Tables in the current worksheet using Me.PivotTables. Me refers to the worksheet where the code is placed.

• Step 3: Within the loop, the code calls the RefreshTable method on each Pivot Table (pt.RefreshTable). This action refreshes the data in each Pivot Table, ensuring that they display the latest information from their respective data sources.

• Step 4: After refreshing the Pivot Table, the loop continues to the next Pivot Table in the worksheet.

• Step 5:  Save the macro.

By executing this code, you automate the process of refreshing all Pivot Tables in the current worksheet when the worksheet is deactivated (i.e., when you switch to another worksheet or close the workbook), ensuring that they are up-to-date with the latest data from their respective sources.

Here is the full code:

Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
For Each pt In Me.PivotTables
pt.RefreshTable
Next pt
End Sub

Important Things to Note

• Using VBA Refresh Pivot Table by Name, you can refresh a specific Pivot Table by referencing its name, ensuring precision in updating.
• VBA Refresh Pivot Table on Another Sheet allows you to refresh Pivot Tables located on different sheets within the same workbook, facilitating consistency in data presentation.
• Consider adding error handling in your VBA Refresh Pivot Table code to handle unexpected issues. It can prevent your code from crashing Excel.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Refresh Pivot Table, with it features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Refresh Pivot Table. Here we learn the 4 methods to auto refresh Pivot Table data using Excel VBA code, along with examples. You can learn more from the following articles –

• VBA Pivot Table
• Pivot Table Change Data Source
• Conditional Formatting In Pivot Table