Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Automatically Refreshing Your Pivot Table
  1. Denym says:

    Is there a way to edit this so it refreshes multiple pivot tables from one data source?

    • Tom Urtis says:

      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

Leave a Reply to Denym Cancel reply

Your email address will not be published. Required fields are marked *

*