Excel Automatically change Pivot Table source data range
Table of Contents
Excel- Automatically change the Pivot Table source data range. When you change the source data of a Pivot Table you need to click 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. The best way to get round this problem is to convert your original range to an Excel Table and then use the table reference as the source data for your Pivot Table. The table reference automatically expands or contracts to fit the range.
Pivot Table source data range
If you want to use 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. This article shows you how to automate the process of redefining the source data range using a macro.
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 macro.
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:
Private Sub Worksheet_Activate()
Dim PTRange As Range
‘Reset the data source and refresh.
Set PTRange = Sheets(“Sheet1“).Range(“A1“).CurrentRegion
ActiveSheet.PivotTables(“PivotTable2)”).ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=PTRange)
ActiveSheet.PivotTables(“PivotTable2“).PivotCache.Refresh
End Sub
You need to be a wee bit careful with the macro above and check the following:
- The example assumes that your Pivot Table source data range is on “Sheet1” of the workbook and is a range that starts with cell reference “A1”. Should this not be the case then you must change the following line as required:
“Set PTRange = Sheets(“Sheet1“).Range(“A1“).CurrentRegion” - The example assumes that the name of your Pivot Table is “PivotTable2“. To find out the name of a specific Pivot Table, right-click one of its cells and choose Pivot Table Options from the short cut menu.
Visual confirmation
Private Sub Worksheet_Activate()
‘The updating and refreshing macro goes here……see above
‘Show the message.
Application.StatusBar = “Pivot Tables updated at “& Time
End Sub
Private Sub Worksheet_Deactivate()
‘Clear message.
Application.StatusBar = False
End Sub
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.
I hope you managed to follow the “Excel- Automatically change the Pivot Table source data range” article. It looks complicated but it’s only really copying and pasting bits of VBA code.