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:

---

Private Sub Worksheet_Activate()
    Dim PTRange As Range

    'Reset the data source.
    Set PTRange = Sheets("Sheet1").Range("A1").CurrentRegion

    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        PTRange, _
        Version:=xlPivotTableVersion10)
 
    'Refresh PT.
    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 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

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.

---

Private Sub Worksheet_Activate()
 
    'The updating & refreshing macro goes here…...see above
 
    'Show 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

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.

Related Posts

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Training Courses

If you want to find out more about Excel Pivot Tables or Macros then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.