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.
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.