When you update a pivot table’s source data in Excel, the pivot table does not update automatically. You can set the pivot table to update when the Excel file opens, but that doesn’t help if you’re making changes after the file opens. If you can use macros in your Excel file, you can use an event procedure to force the pivot table to refresh automatically if the source data changes.
Macro to Refresh Automatically
If you usually make minor changes to the existing data, you can add event code to the data sheet, which will force the pivot table to refresh automatically after each change.
NOTE: This solution is not ideal if you make numerous changes to the data sheet.
In this example, the source data is on a sheet named “Forecast”, and the pivot table is on the “Product” sheet. To add the event code, follow these steps:
Right-click on the the sheet tab for the Forecast sheet, and click View Code
Copy the following code to the Forecast worksheet module, to force the pivot table to refresh automatically
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Worksheets("Product").PivotTables(1).PivotCache.Refresh
Application.EnableEvents = True
End Sub
The worksheet module in your workbook might not have Option Explicit at the top. You can type that line in, if it’s missing.
Unprotect the Worksheet
If the pivot table worksheet is protected, you can add code to unprotect it, refresh automatically, and then protect it again.
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Product")
Application.EnableEvents = False
.Unprotect Password:="yourpassword"
.PivotTables(1).PivotCache.Refresh
.Protect Password:="yourpassword"
Application.EnableEvents = True
End With
End Sub
Test Refresh Automatically Code
To test the pivot table refresh code, make a change to the data on the Forecast sheet.
- Making the change will trigger the Forecast sheet’s Change event.
- In the Event code, the pivot cache for the Product sheet’s pivot table will refresh automatically
More Information
There’s more information on Excel pivot tables and pivot table source data on my Contextures website.
- Create a Pivot Table in Excel 2007
- Excel Pivot Table — Dynamic Data Source
- Automatically Include New Data in a Pivot Table
___________________
Nice article, Debra!
Some notes:
1) If your PT is on a separate worksheet from your source data (which is what I do nearly all the time), the SheetActivate event for the worksheet holding the PT might be the better place to hold the refresh code. That way, if I make multiple updates in the source data, I don’t get multiple refreshes; rather, I just get the one refresh when I actually visit that PT again.
2) I also like using the workbook events rather than the sheet events. That way, if my workbook contains multiple PTs, one event sub at the workbook level can manage updates for all PTs in the workbook
3) Refreshing the PT is a must, but we also need to make sure we keep up with changes in the size of the source range for the PT. I typically do that with dynamic named ranges in Excel 2003 or earlier, or Tables in Excel 2007/2010.
Last year I wrote an article ; I’d love to get your feedback on it.
Cheers,
Patrick
great job!
Excellent. This is just what a was looking for: a live update mechanism for a Pivot Table.
I appreciate it.
You saved my bacon. So nicely and trouble free. Thank you.
I just used the code that you provide to unprotect worksheet then refresh the pivot table and then protect the worksheet.NOw the problem is that when I enter data in my data worksheet and press the tab key , it’s not working , I can’t use the tab key.Please help me with this..
@Chetan Is the data entry sheet protected too? Are the cells for data entry unlocked?
If you unprotect the sheet, can you use the tab key?
No.The data entry sheet is not protected.Yes.. The cells for data entry unlocked.If I unprotected the sheet and enter any value in the data entry sheet than code that I wrote in the data entry sheet takes place and tab key stopped working..Please give me your email address if possible,so I can send you the attachment and you can better understand my problem.. And thanks for replaying me..
No.The data entry sheet is not protected.Yes.. The cells for data entry unlocked.If I unprotected the sheet and enter any value in the data entry sheet than code that I wrote in the data entry sheet takes place and tab key stopped working..Please give me your email address if possible,so I can send you the attachment and you can better understand my problem.. And thanks for replaying me..
This got me stuck in a loop. Better to use below.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Worksheets(“Product”).PivotTables(1).PivotCache.Refresh
Application.EnableEvents = True
End Sub