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.
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:
Paste the following code into the large white area that is your worksheet module.
Me keyword refers to the parent worksheet object.
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