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:
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
'Clear message.
Application.StatusBar = False
End Sub
 |
| Excel's Status Bar |
Related Posts
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.
