Create a button to refresh pivot tables in a file

[Pierre Gerrits]

I have a large data query file with a few sheets containing pivot tables. I need to add a button to the first sheet that will refresh all the pivot tables in the file.

[#2]

Hello Pierre

Excel already has such a button on the ribbon:

Data -> Connections -> Refresh All

You could also put it in the Quick Access Toolbar if you don't want to switch tabs.

[#3]

Dear Pierre,

First and foremost, add the (Developer Tab) to your Excel Ribbon, and follow these steps:

  - Go to Insert at the Developer Tab, and click on it.

  - You will find two sections: Forms Controls and ActiveX Control, in the ActiveX Control Section, select the command button, and add it to your sheet.

  - Double click on the button, to open the VBA Editor, but before that, make sure that the design mode is turned on.

  - Copy this command, and paste it in the VBA editor: 

ActiveSheet.PivotTables("Your Pivot Table Name").PivotCache.Refresh

So it would look as follows:

  - Add your pivot table name to the code, and save it.

  - Close VBA Editor

  - Turn Design Mode off

  - You have to save your workbook in one of these formats: (XLSM, XLSB, XLS), in order to save Refresh code.

Enjoy :)

[#4]

HI Pierre

If you do want to go down the Macro route rather than using the Refresh All button then I'd suggest adding this code to a module.

Best to avoid any hard coded referencing to Pivot Table names or sheets etc.

Sub RefreshAllPivots()

Dim pc As PivotCache

     For Each pc In ThisWorkbook.PivotCaches

          pc.Refresh

    Next pc

End Sub

[#5]

Also, when you say Data Query file are you using Power Query and do you want the queries to refresh also,  in which case my PivotCache suggestion won't do it for you

This instead....

Sub RefreshAll

ThisWorkbook.RefreshAll

EndSub

[#6]

Hi there!

I think I have similar concern here.

I have several excel files with pivot tables each and with at least two sheets and I want to automatically refresh in one click of a button all these files which are all connected to db server.

Would really appreciate your responses.

Thanks!

[#7]

Hi Dana

Just to clarify, do you want to open multiple files, refresh each, then save and close them?