How To Auto Update Pivot Table Using VBA in Microsoft Excel 2010

In this article, you will learn how to automatically update pivot table as you make any changes. We will use VBA code to make automated pivot report.
 
Let us take an example:

We have Sales Report that contains Product & Month category.
 

 
Following is a snapshot of pivot table where we are showing Product performance for January month
 

 
To make pivot work dynamically (update pivot report as the contents get modified in Sheet1), follow the below mentioned steps:
 
Click on Developer tab

From Code group, select Visual Basic
 

 
Enter the following code in Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("Sheet1").PivotTables("Pivot1").RefreshTable

End Sub

Note:Pivot1 is the name of the Pivot table which is assigned by default as you create it.
 

 
To check if the code is working fine or not, let us change any value (cell C3 from 100 to 1000 for Product 2)
 

 
In this way, you can make automatic pivot table which will update as the data changes.