VBA Refresh Pivot Table

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Refresh Pivot Table

When we insert a Pivot Table in the sheet, Pivot Table data does not change itself once the data changes. So, we need to do it manually. But in VBA, there is a statement to refresh the Pivot Table: expression.refreshtable. Using this, we can refresh the Pivot Table by referencing the worksheet or refer to the entire Pivot Tables in the worksheets and refresh them all at once.

The Pivot Table is vital in analyzing the huge amount of data. It helps in analyzing, summarizing, and making useful data interpretations. However, one of the problems with this Pivot Table is it will not automatically get refreshed if there is any change in the source data. Instead, the user has to refresh the Pivot Table by going to the particular Pivot Table whenever there is a change. But say goodbye to the manual process because here, we have the method to refresh the pivot tableRefresh The Pivot TableTo refresh pivot tables, you may use the following methods - refresh pivot table by changing data source, refresh pivot table using right click option, auto-refresh pivot table using VBA Code, refresh pivot table when you open the workbook.read more as soon as you make any change in the Pivot Table.

VBA-Refresh-Pivot-Table

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Refresh Pivot Table (wallstreetmojo.com)

How to Auto Refresh Pivot Table Data VBA Code?

The only time the pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more needs to be updated is whenever there is any change in the source data of the Pivot Table we are referring to.

For example, look at the below data and Pivot Table.

Auto Refresh Pivot Tables

Now, we will change the numbers in source data: from A1 to B17.

Auto Refresh Pivot Tables 1

In cell B9, we have to change the value from 499 to 1499, i.e., a 1000 increase in the data. But, if you look at the pivot it still shows the result as 4295 instead of 5295. So, we have to refresh my Pivot Table to update the Pivot Table manually.

To overcome this issue, we need to write a simple excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more code to refresh the Pivot Table whenever there is any change in the source data.

You can download this VBA Refresh Pivot Table Excel Template here – VBA Refresh Pivot Table Excel Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#1 – Simple Macro to Refresh All Table

Step 1: Change the Event of the Datasheet

We need to trigger the change event of the datasheet. Then, in the Visual Basic Editor, double-click on the datasheet.

Refresh Pivot Tables Example 1

Once you double-click on the sheet, select “Worksheet” and select the event as “Change.”

Refresh Pivot Tables Example 1-1

You will see an auto sub procedure opened as Worksheet_Change(ByVal Target As Range)

Refresh Pivot Tables Example 1-2
Step 2: Use Worksheet Object

Refer to the datasheet by using the Worksheets object.

Example 1-3
Step 3: Refer Pivot Table by Name

Refer to the Pivot Table name by the name of the Pivot Table.

Example 1-4
Step 4: Use the Refresh Table Method

Select the method as “Refresh Table.”

Example 1-5

Now, this code will refresh the Pivot Table “PivotTable1” whenever there is any change in the source data sheet. You can use the below code. First, you have to change the Pivot Table name.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

  Worksheet("Data Sheet").PivotTables("PivotTable1").RefreshTable

End Sub

#2 – Refresh All Pivot Tables of the Same Worksheet

If you have many Pivot Tables in the same worksheet, you can refresh all the Pivot Tables in a single click itself. For example, use the below code to refresh all the Pivot Tables in the sheet.

Code:

Sub Refresh_Pivot_Tables_Example1()
  Worksheets("Data Sheet").Select

  With ActiveSheet
    .PivotTables("Table1").RefreshTable
    .PivotTables("Table2").RefreshTable
    .PivotTables("Table3").RefreshTable
    .PivotTables("Table4").RefreshTable
    .PivotTables("Table5").RefreshTable
  End With

End Sub

It would help if you changed the name of the worksheet and Pivot Table names per your worksheet details.

#3 – Refresh All Tables in the Workbook

We are unlikely to have all the Pivot Tables on the same worksheet. Usually, for each report, we try to add separate pivot tables in separate sheets. We cannot keep writing the code for each Pivot Table to refresh in these cases.

So, we can use a single code using loops to loop through all the Pivot Tables on the workbook and refresh them with a single click of the button.

The below code will loop through each Pivot Table and refresh them.

Code 1:

Sub Refresh_Pivot_Tables_Example2()

  Dim PT As PivotTable

  For Each PT In ActiveWorkbook.PivotTables
    PT.RefreshTable
  Next PT

End Sub

Code 2:

Sub Refresh_Pivot_Tables_Example3()

  Dim PC As PivotCache

  For Each PC In ActiveWorkbook.PivotCaches
    PC.Refresh
  Next PT

End Sub

Both the codes will do the refreshing Pivot Tables.

If you want the Pivot Table to refresh as soon as there is any change in the datasheet of the pivoting sheet, you need to copy and paste the above codes to the Worksheet Change event in that workbook.

#4 – Avoid Loading Time by using Worksheet Deactivate Event

When we use the “Worksheet Change” event, it keeps refreshing even when there is no change in the data source, but if any change happens in the worksheet.

Even if you enter one single dot in the worksheet, it tries to refresh the Pivot Table. So to avoid this, we can use the “Worksheet Deactivate” method instead of the “Worksheet Change” method.

Worksheet Deactivate Event

Deactivate the event updates on the Pivot Table when moving from one sheet to another sheet.

Recommended Articles

This article is a guide to the VBA Refresh Pivot Table. Here, we learn how to auto-refresh Pivot Tables using Excel VBA Macros code, examples, and a downloadable template. Below are some useful Excel articles related to VBA: –