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 automatically update the pivot table if the source data
changes.

Add the Event Procedure Code

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

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Product").PivotTables(1).PivotCache.Refresh
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, and then protect it again.

Private Sub Worksheet_Change(ByVal Target As Range)

    With Worksheets("Product")
        .Unprotect Password:="yourpassword"
        .PivotTables(1).PivotCache.Refresh
        .Protect Password:="yourpassword"
    End With

End Sub

Test the Refresh 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 is refreshed.

More Information

• Create a Pivot Table in Excel 2007
• Excel Pivot Table -- Dynamic Data Source
• Automatically Include New Data in a Pivot Table

___________________