Checkout my article on VBA Code Modules & How to Run Macros Based on User Events to learn more about the sheet modules and events.Next, just below the Worksheet_Change line, type in this instruction: The Refresh All method will refresh all the pivot tables, queries, and data connections in the workbook.Also, if you are new to pivot tables, I have a series to walk you through what they are and how to use them.
This adds a new event at the top called Worksheet_Change.
Now we will highlight and delete the unnecessary code below it.
The refresh puts the event in a recursive loop, and can end up crashing Excel. One way to check if the macro is working is to test it.
Make a change to the source data and see if it is reflected in your pivot table. The keyboard shortcut to toggle a breakpoint on/off is: Now whenever an action occurs that triggers the macro, Excel will jump to the VB Editor and pause the macro so that you can check the code.
If you don’t see the Developer tab, you can make it visible using the instructions here.
You only have to do this once, and then the Developer tab will always be visible every time you open Excel in the future.
That will add a Worksheet_Selection Change event to the module, which we don’t actually want, so we will delete it in just a moment.
Before we do, let’s go to the Procedure drop-down menu on the right and choose Change.
Within the code module, we want to create an event macro.
To do so, choose Worksheet in the Object drop-down box on the left.
I also share a non-macro solution to update the pivot tables when the file is opened. Skill Level: Intermediate If you learn best by doing it on your own, you can download the file I'm using in the video to follow along. Refresh Pivot Table (41.5 KB)Can your pivot tables be updated immediately and automatically when their source data changes? It requires the use of a really simple macro that I will show you how to create below.