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: 

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

Exercise Workbook:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK


This is our data:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

These are the two pivot tables using this data source:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

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

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

STEP 1: Go to Developer > Code > Visual Basic

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

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

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

STEP 3: Let us test it out!

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

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

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

 Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

How to Refresh All Pivot Tables Using Macros In Excel

 

101 Macros Book

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn