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

Get FREE Advanced Excel Exercises with Solutions!

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.

Example to refresh pivot table in Excel with VBA


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.

VBA to Refresh One Pivot Table in Excel

  • 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.

VBA to Refresh All Pivot Tables in the Active Worksheet in Excel

  • 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.

VBA to Refresh All Pivot Tables in Multiple Workbook in Excel

  • 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.

VBA to Refresh the Pivot Table Cache in Excel

  • 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.

VBA to Auto-Refresh Pivot Table While Changing Data in Excel

  • 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo