Auto updating pivot table
While refreshing a Pivot table is as easy as two clicks, you still need to do this every time there is a change.To make it more efficient and auto-refresh the Pivot Table whenever there is a change in the data source, you can use a simple one-line VBA macro code.(2) Select any cell in the specified pivot table, you will get its name on the Analyze tab (or Options tab). If the Pivot Table is on the same sheet that changes data, you need avoid a loop, so you need implement a conditional statement Private Sub Worksheet_Change(By Val Target As Range) If Intersect(Target, Range("M: M")) Is Nothing Then Exit Sub Else 'Msg Box "You just changed" & Target. Bottom Line: Learn how to use a simple macro to refresh pivot tables automatically whenever changes are made to the source data.To do this: Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option.You can simply refresh the Pivot Table and it’ll account for the new rows/columns.Once you have created a Pivot Table, it doesn’t automatically refresh when you add new data or change the existing data.
Refresh | Change Data Source Any changes you make to the data set are not automatically picked up by the pivot table. On the Analyze tab, in the Data group, click Change Data Source. This will be quite boring if the pivot table stays in another worksheet and you may forget to refresh it.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: ).Under that workbook are listed the sheets within the workbook. Double-clicking on the sheet opens up the code module for that object.