Updating pivot tables with new data
We can add VBA code to the Worksheet_Change event to perform actions when the user edits cells.
Note: The Selection Change event that is added by default will run any time the user selects a cell in the sheet.
You only have to do this once, and then the Developer tab will always be visible every time you open Excel in the future.
In the Project Explorer window of the Visual Basic editor, locate the workbook that you want to change. If you don’t see the Project Explorer window you can enable it from the View menu (keyboard shortcut: ).
Watch the first video in that series on Pivot Tables & Dashboards To automatically update our pivot tables, we are going to write a macro with one simple instruction.
That instruction basically says: when I make a change to my worksheet, refresh all the pivot tables and data connections. You can do this by clicking the Visual Basic button on the Developer tab of the ribbon.
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.In the VB editor, you can click on the gray column just to the left of your Worksheet_Change macro. You can then press to run to the end (or next breakpoint).If you make a change to the worksheet and Excel doesn’t pull you into the VB Editor, you know there is a problem with the macro not running.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.This action is the same as if you manually click the Refresh button on the Data tab.
To do so, choose Worksheet in the Object drop-down box on the left.