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:

Visual confirmation

If you want to visually confirm that your Pivot Table has been refreshed you can place an unobtrusive message down the bottom of the Excel window on the Status Bar. Add the following line to your Worksheet_Activate macro and clear the message away when you activate a different sheet using the Worksheet_Deactivate event.

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.

Further reading