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.
 
img1
 
Following is a snapshot of pivot table where we are showing Product performance for January month
 
img2
 
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
 
img3
 
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.
 
img4
 
To check if the code is working fine or not, let us change any value (cell C3 from 100 to 1000 for Product 2)
 
mg5
 
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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube