When you need to refresh or update data in an existing PivotTable after creating it, you must use the Change Data Source command. However, manually updating the pivot table is inconvenient and time-consuming. As a result, you’ll need to figure out how to create an automated pivot table that will automatically update data. So, in this tutorial, we will show you how to automatically update a pivot table when source data changes.
We’ve included a data set with information on sales details in various regions in the image below. You can assume that our data set is quite large. So, for a dynamic purpose, we need an automated process every time we update data. To do so, we’ll run a VBA code and apply it to the data set below. To complete the task, follow the steps below.
Step 1: Creating an Excel Pivot Table with a Source Data Range
- Firstly, click on the Insert tab to insert a pivot table.
- Then, 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 to place the pivot table on a new worksheet.
- Then, click OK.
- In a new worksheet (Sheet4), the pivot table will be created.
- As you can see in the image below, 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 Visual Basic Editor to Apply 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 with Your Sheet Containing Data Set
- To create a worksheet event for Sheet2, select the Worksheet option from the list.
 Notes: A worksheet event means that any changes you make to the source data will be reflected in your program immediately. That’s why 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 one to keep only the change event.
Step 5: Declaring All Variables with an Option Explicit Statement
- To declare all variables and to find the undeclared variables, type Option Explicit at the top of the program page.
Step 6: Inserting VBA Code to Update Excel Pivot Table Automatically
- Get the completed VBA code and paste it.
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 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 see that the changed price value (£113) is updated.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope this article has given you a tutorial about how to automatically update a pivot table when sourcing data changes in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
Please contact us if you have any questions. Also, feel free to leave comments in the section below. Stay with us and keep learning.
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
