---

MS Excel 2003: Automatically refresh pivot table when user switches between sheets

This Excel tutorial explains how to write a macro to automatically refresh a pivot table when the user switches between sheets in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: I have so many cascading calculations in Microsoft Excel 2003/XP/2000/97, and the pivot table was refreshing after each calculation rather than at the end. I want to set up the refresh to occur after all calculations have been made. For example, if I switch over to the Pivot Table (or the chart that it's based on).

Answer: One of our viewers suggested that it is sometimes more efficient to automatically refresh the pivot table when the "Workbook_SheetDeactivate" event fires. This will ensure that the pivot table only refreshes when the chart, workbook, or worksheet is deactivated. Thus, avoiding multiple refreshes if you have cascading calculations.

Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, there are two sheets - one is called Data which contains the source data for the pivot table. Another sheet is called Pivot which contains the pivot table.

On the workbook, we've placed code on the "Workbook_SheetDeactivate" event, so that whenever the user switches between the Data and Pivot sheets, the pivot table will refresh.

You can press Alt+F11 to view the VBA code.

Macro Code

The macro code looks like this:

Private Sub Workbook_SheetDeactivate()

   'If the chart, workbook or worksheet is deactivated, refresh the pivot table
   Sheets("Pivot").PivotTables("PivotTable1").RefreshTable

End Sub