Forum Discussion
Create a button to refresh pivot tables in a file
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.
6 Replies
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
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!
- Hi Dana
Just to clarify, do you want to open multiple files, refresh each, then save and close them?
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 SubDear 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 :)
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.
