Refresh All Pivot Tables Using Macros In Excel

It is so frequent that whenever I update my data, I forgot to refresh my Pivot Tables. We can now refresh all pivot tables using Excel Macros!

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Refresh all Pivot Tables

Copy Source Code:

Sub RefreshAllPivotTables()

Dim pCache As PivotCache

'With just one loop, refresh all pivot tables!
For Each pCache In ActiveWorkbook.PivotCaches
pCache.Refresh
Next pCache

End Sub

Final Result: 

Exercise Workbook:

---

This is our data:

These are the two pivot tables using this data source:

Now let’s make change to one big sales value! Let us see if the pivot tables will reflect these values.

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

 

Make sure your macro is selected. Click Run.

 

With just one click, all of your pivot tables are now refreshed!

 

 

How to Refresh All Pivot Tables Using Macros In Excel