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.
Share Improve this question edited Mar 15 at 17:04 braX 11.8k5 gold badges22 silver badges37 bronze badges asked Mar 13 at 14:52 MICHAELG1117MICHAELG1117 111 silver badge3 bronze badges1 Answer
Reset to default 0Power 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).