Automatically Update a Pivot Table When Source Data Changes in Excel

We’ve included a data set with information on sales details in various regions in the image below. We need an automated process to update data in a Pivot Table.

Step 1 – Creating an Excel Pivot Table with a Source Data Range

Click on the Insert tab.
Click on the PivotTable command from the PivotTables group.
Select the From Table/Range option from the list.

Select the table range with the header.
Choose the New Worksheet option.
Click OK.

The pivot table will be created in a new sheet.
You can choose the fields (Region, Branch, Price, Quantity, etc.) to appear in the pivot table.
Select the PivotTable command from the PivotTable Analyze. You can find the name of your pivot table (PivotTable2) there and edit it as needed.

Step 2 – Opening the Visual Basic Editor to Apply the VBA Code

Press Alt + F11 to open the Visual Basic Editor.
From the VBA Excel Objects, double-click to select the worksheet name (Sheet2) where your data set is located.

Step 3 – Creating a Worksheet Event

Select the Worksheet option from the dropdown list on (General).

A worksheet event means that any changes you make to the source data will be reflected in your program immediately. The program will run automatically when you make any changes to the source data set.

Step 4 – Inserting a New Event for Change in Worksheet

Choose Change instead of SelectionChange to create a new worksheet event.

Delete the previous code.

Step 5 – Declaring All Variables with an Option Explicit Statement

Type Option Explicit at the top of the program page.

Step 6 – Inserting VBA Code to Update Excel Pivot Table Automatically

Copy the following code into the window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet4 is the sheet name of PivotTable location
'PivotTable2 is the PivotTable Name
Sheet4.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Step 7 – Running the VBA Code to Get Changes in Results

Press F5 to run the VBA code.
Make a change to the data set to see if the program is still working. For example, in the IRE region, the price value for the Belfast branch has been changed to £113.