How to create a Refresh All button in Excel

It’s easy to forget to refresh your Pivot Tables when you add new source data. Here’s an effective solution to that problem: Add a Refresh button in your Excel report!

This is very easy, and only takes a minute:

There are 3 steps:

Step 1: Go to the Insert menu, choose Icons and type “Refresh” in the search field. Choose the icon you want, and click on Insert. Resize and place the icon where you want to have it.

Step 2: Open the VBA editor (Alt + F11). Go to the Insert menu and choose Module. Type this code:

Sub RefreshAll()
ActiveWorkbook.RefreshAll
End Sub

Step 3: Go back to Excel, right-click on the Refresh Icon and choose “Assign Macro”. Choose the “RefreshAll” macro and click OK.

The next time you add data to your Pivot Table, or you want to get the most recent data from your Datawarehouse, simply click on your new Refresh button!

Important: When you save the workbook, you need to save it as either Excel Macro-Enabled Workbook or Excel Binary Workbook!

BONUS TRICK:

Add one more line to the code and get a popup message when the refresh is done:

Sub RefreshAll()
ActiveWorkbook.RefreshAll
MsgBox “All tables refreshed!”
End Sub

Of course, you can write any text you want between the double quotes.

More Excel tutorials:

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

9 thoughts on “How to create a Refresh All button in Excel

  1. But then this only refreshing pivot’s?
    Not the entire file if you’re f.e. working in a read-only and someone else’s working in the file?

  2. That’s probably right. But if you just want to recalculate when someone else is working in the file, you can usually press F9 to see saved changes. In Excel 365 you may be able to see it in real time, depending on the settings.

  3. Thanks! it works. However, it fails in the second part when the message “all tables refreshed” is sought. I copied and pasted your code word to word… the debugger opens with message compile error: syntax error…

  4. Can I make this formula work where I click the refresh button and it will reset a count to “0” of one cell?

  5. Pingback: How Do I Refresh All Pivot Tables In Excel VBA? – Erickkasysavane

  6. Pingback: How Do You Refresh A Pivot Table? – Almazrestaurant

  7. Pingback: How Do You Refresh Data In A Pivot Table? – Almazrestaurant

Leave a Reply

Your email address will not be published. Required fields are marked *


× 2 = two