Question:  I have so many cascading calculations in Excel, 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_Deactivate" 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 take a 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_Deactivate" 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_Deactivate()

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

End Sub