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)


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.


Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube