Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table
If you’d appreciate the convenience of having your pivot table automatically refresh whenever you change its source data, here’s how you can accomplish that.
In the Before picture, the source data’s State field lists American states. In the After picture, you’ve changed the State names from their two-letter abbreviation to a regional theme.
Before
After
Whether you change the State field or numbers in the Amount field, when you want to see those changes immediately represented in your pivot table, follow these simple steps:
Step 1
Right-click your worksheet tab, and left-click to select View Code.
Step 2
Paste the following code into the large white area that is your worksheet module.
The Me keyword refers to the parent worksheet object.
The CurrentRegion property can be used because the source data is contiguous…no empty rows or columns are contained within the source data itself. Although any cell address existing in the source data range could have been used, I kept it simple and just used A1.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub
Me.PivotTables(1).RefreshTable
End Sub
Step 3
Press Alt+Q to return to your worksheet.
Is there a way to edit this so it refreshes multiple pivot tables from one data source?
PivotTables are objects for which there is a Collection object, just as there is for worksheets and workbooks. As you might guess, the name of the Collection object for PivotTables is PivotTables, and you can loop through every PivotTable on a worksheet or throughout the workbook if you need to.
For example, if you have more than one PivotTable on a worksheet and they are tied to the same source list that starts in cell A1, this Worksheet_Change event refreshes all PivotTables on that worksheet automatically when the source data is changed:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“A1”).CurrentRegion) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
Dim PT As PivotTable
For Each PT In ActiveSheet.PivotTables
PT.RefreshTable
Next PT
End Sub
Suppose you have several PivotTables on many different worksheets and you want to be confident that every PivotTable displays the current data from its respective source lists. This refreshes every PivotTable in the workbook when the source data changes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“A1”).CurrentRegion) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
Dim wks As Worksheet, PT As PivotTable
For Each wks In Worksheets
For Each PT In wks.PivotTables
PT.RefreshTable
Next PT
Next wks
End Sub