How to Refresh Pivot Table with VBA in Excel (5 Examples)

Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to refresh the pivot table in Excel using VBA.

Following is the example of our pivot table which we will be using throughout the whole article and show you 5 different examples of refreshing pivot tables in Excel with VBA code.

1. Utilizing VBA to Refresh One Pivot Table in Excel

If you want to refresh just one pivot table in your Excel worksheet then,

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Sub RefreshOnePivotTable()
   ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

Your code is now ready to run.

Here, PivotTable1 is our Pivot Table name. You write the name that your Pivot Table has.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

This will refresh the pivot table in your existing Excel worksheet.

2. Refreshing All Pivot Tables in the Active Worksheet with Macro

To refresh all pivot tables in the active worksheet, follow the steps below.

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub RefreshActiveSheetPivotTables()
   Dim PivotTbl As PivotTable
   For Each PivotTbl In ActiveSheet.PivotTables
      PivotTbl.RefreshTable
   Next PivotTbl
End Sub

Your code is now ready to run.

• Run the macro and all the pivot tables in your active worksheet will be refreshed.

Read More: How to Refresh All Pivot Tables in Excel

3. Applying VBA Code to Refresh All Pivot Tables in Multiple Workbook

If you want to refresh all the pivot tables in multiple workbooks at once with VBA code then the steps are:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub RefreshAllPivotTables()
  Dim PivotTbl As PivotTable
  For Each PivotTbl In ActiveWorkbook.PivotTables
     PivotTbl.RefreshTable
  Next PivotTbl
End Sub

Your code is now ready to run.

• Run the code and all the pivot tables in all of your open Excel workbooks will be refreshed.

Remember to keep all of the workbooks open while running this code.

4. Refreshing the Pivot Table Cache with Excel VBA

If you have multiple pivot tables in your workbook that use the same data, you can refresh only the pivot table cache rather than refreshing the actual pivot table all the time. Refreshing the cache automatically clear the cache memory of all the pivot tables using the same data connection in the cache.

Steps to do that are,

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub RefreshPivotTableCache()
   Dim PivotCache As PivotCache
   For Each PivotCache In ActiveWorkbook.PivotCaches
       PivotCache.Refresh
   Next PivotCache
End Sub

Your code is now ready to run.

• Run the code and all the pivot table cache memories will be cleared.

Read More: How to Auto Refresh Pivot Table without VBA in Excel

5. Auto-Refreshing Pivot Table with Change of Data Using VBA Macro

What if you have a pivot table that has a large amount of data and all you want is to update a few data while having the whole table untouched? In Excel, you can auto-refresh the pivot table while updating data with VBA.

• Open Visual Basic Editor from the Developer.
• On the left side of the editor, there would be a Project Explorer pane that has all the names of the worksheet.
• In Project Explorer, double-click on the sheet name that contains the pivot table.

• The code window will appear where we will create an event macro. Choose Worksheet from the Object drop-down box on the left of the code module. This will add a Worksheet_SelectionChange event to the module, we actually don’t need it so we will delete this piece of code later.
• Then click on the Procedure drop-down list and select Change. This adds a new event at the top of the code module called Worksheet_Change. We will write our code here so delete the code generated by the Worksheet_SelectionChange

• Now copy the following code and paste it in the Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("PivotTbl").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Your code is now ready to run.

Here, PivotTbl is the worksheet name in our Excel workbook and PivotTable1 is our Pivot Table name. You write the name that your worksheet and Pivot Table has.

• Now every time you change the data in the original data table in your worksheet, the data in the pivot table will automatically be updated.

Read More: How to Auto Refresh Pivot Table in Excel

Download Practice Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article shows you how to refresh the pivot table in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Further Readings

• How to Update Pivot Table Range
• How to Automatically Update a Pivot Table When Source Data Changes
• How to Refresh All Pivot Tables with VBA
• Pivot Table Not Refreshing