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.
- Go back to your pivot table, and the changed price value (£113) is updated.
Download the Practice Workbook
Related Articles
- How to Auto Refresh Pivot Table in Excel
- How to Auto Refresh Pivot Table without VBA in Excel
- How to Refresh All Pivot Tables in Excel
- How to Update Pivot Table Range
- Pivot Table Not Refreshing
- How to Refresh All Pivot Tables with VBA
- How to Refresh Pivot Table with VBA in Excel
