Automatically Refresh a Pivot Table

It would be nice if a pivot table automatically updated, whenever its source data changes, but unfortunately, that doesn’t happen.

If you add new records, or delete records, or edit the existing data, the pivot table doesn’t show the revised data right away.

To keep the pivot table up-to-date, you have 3 choices:

  1. Manually refresh the pivot table
  2. Use programming to update it
  3. Change a pivot table setting, to get some automatic updates

Manually Refresh

A quick and easy way to refresh the pivot table after the data changes is to manually update it:

  • Right-click any cell in the pivot table, then click on Refresh.

pivotrefresh01

Refresh When File Opens

Another way to update is to set the pivot table to refresh when you open the file that it’s in. With this method, you’ll get partial automation, without having to add macros to the file. Any time you close then re-open the file, the pivot table will be refreshed.

To set this up:

  1. Right-click any cell in the pivot table
  2. Click PivotTable Options
  3. In the PivotTable Options window, click the Data tab
  4. In the PivotTable Data section, add a check mark to Refresh Data When Opening the File
  5. Click OK to close the dialog box.

pivotrefresh02

Refresh With Programming

The final way to update the pivot table is with programming. You can use Excel VBA to automatically update a pivot table when its worksheet is activated.

There are instructions on my Contextures website, for copying VBA code to your own files.

Put the following code on the worksheet module, if there is one pivot table on the sheet:

Private Sub Worksheet_Activate()
   Me.PivotTables(1).RefreshTable
End Sub

If there are multiple pivot tables on the sheet, with different source data, use this code, pasted onto the worksheet module:

Private Sub Worksheet_Activate()
   Dim pt As PivotTable
   For Each pt In Me.PivotTables
      pt.RefreshTable
   Next pt
End Sub

_______________________________

This entry was posted in Refresh. Bookmark the permalink.

4 Responses to Automatically Refresh a Pivot Table

  1. Steven Drake says:

    Great help than you very much

    I had the issue of my Pivot tables updating before the External Data Source refresh had finished.

    So on the sheets that hold the external data source I added, you code to update PivotTables on data change (I think its working, and data and Pivot tables in Sync)

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PT As PivotTable
    Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

    For Each PT In WS.PivotTables
    PT.RefreshTable
    Next PT

    Next WS
    End Sub

    Thanks

    • Patrick Idemudia says:

      Hi Steven,

      Good day to you. I tried using this code and it keeps crashing my workbook. Can you be of help please?

      Patrick

  2. Pingback: Automatically Update Data in Pivot Table

  3. Johnny Larsen says:

    Private Sub Worksheet_Activate()
    PivotTables(“PivotTable1”).PivotCache.Refresh
    End Sub

Leave a Reply

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