Excel- Automatically change the Pivot Table source data range
When you change the source data of a Pivot Table you need to Refresh to reflect these changes but if you add any new rows to the end or columns to the sides of the list, refreshing the data does not detect these additions to the original source data range unless you converted your original range to a Table and then used the Table as the source data for your Pivot Table.
If you used the original range as your data source then you will have to update the definition of the Data Source by clicking the Change Data Source control on the Options tab of PivotTable Tools. And you will have to repeat this process every time you add any new rows to your list.
Changing the Data Source range automatically
We saw in an earlier article, Make Pivot Tables refresh automatically how to refresh a Pivot Table automatically using the Worksheet_Activate event.
You can change the Worksheet_Activate macro so that in addition to refreshing the Pivot Table it also updates the definition of the Data Source. Thus the Pivot Table responds to any additions or deletions made to the original data range. Copy and paste the section below into your Worksheet_Activate macro:
Excel’s Status Bar is visible at the bottom of the Excel window and your messages are always shown on the left hand side. If you don’t want the time stamp shown in your message then leave out the “& Time” part of the message.