Pinterest Pixel

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.... read more

Download Excel Workbook
Bryan
Posted on

Overview

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

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

Exercise Workbook:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

Download excel workbookRefresh-All-Pivot-Tables.xlsm


This is our data:

Refresh All Pivot Tables Using Macros In Excel

These are the two pivot tables using this data source:

Refresh All Pivot Tables Using Macros In Excel

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

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

 

STEP 3: Let us test it out!

See also  Highlight Top 10 Values of Selection Using Macros In Excel

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

 

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

 Refresh All Pivot Tables Using Macros In Excel

 

How to Refresh All Pivot Tables Using Macros In Excel

 

101 Macros Book

If you like this Excel tip, please share it
Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline
Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

See also  Convert Chart into Image Using Macros In Excel

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...