vba - There A Way to REFRESH ALL Pivots, Charts, and Data Queries all at once in EXCEL when Pushing REFRESH ALL - Stack Overflow

I'm seeing you can use macros to run to refresh everything but I need to find a way when I press REFRESH ALL that it updates all charts and pivots also.

Is there a way to run the macro when you press REFRESH ALL so that when data changes the pivots and charts are automatically updated without having to activate the macro itself? Thanks.

I'm seeing you can use macros to run to refresh everything but I need to find a way when I press REFRESH ALL that it updates all charts and pivots also.

Is there a way to run the macro when you press REFRESH ALL so that when data changes the pivots and charts are automatically updated without having to activate the macro itself? Thanks.

• excel
• vba
• page-refresh

Share Improve this question Follow edited Mar 15 at 17:04 braX 11.8k55 gold badges2222 silver badges3737 bronze badges asked Mar 13 at 14:52 MICHAELG1117MICHAELG1117 1111 silver badge33 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Power Query cannot directly call VBA, and it looks like the Refresh All doesn't generate an event. You can however, simulate the event hook ('trigger macro on Refresh All'): just execute VBA when a specific cell changes, and then ensure that RefreshAll always triggers PQ to change that specific cell.

You really only need the MS tutorial. Set up PQ to modify the cell you watch:
https://learn.microsoft/en-us/office/troubleshoot/excel/run-macro-cells-change)

As a test, I placed NOW() in a table, loaded that table into PQ, and then loaded the PQ output into A2. I modify the MS tutorial code to watch A3 for changes. Running Refresh All makes PQ update A3, and that triggers the VBA (image below shows popup after pressing Refresh All).