Programming Excel with VBA and .NET

Refresh Pivot Tables and Charts

You can get pivot tables to refresh automatically when the workbook opens by selecting Refresh on Open in the PivotTable Options dialog (Figure 13-6). That option is off by default because refreshing a pivot table may take a long time, depending on the size of the pivot table data.

If the pivot table is based on an external data source, you can also select periodic updates on the PivotTable Options dialog. That option isn’t available for pivot tables based on worksheet data and, as mentioned earlier, pivot tables don’t automatically refresh when the source data changes.

To implement automatic refresh for pivot tables based on worksheets, add the following code to the workbook’s class module:

' ThisWorkbook class
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Dim pc As PivotCache
    ' Get each of the pivot caches.
    For Each pc In ThisWorkbook.PivotCaches
        ' If cache is based on a worksheet.
        If pc.SourceType = xlDatabase Then
            ' Update the cache based on the deactivated worksheet.
            If InStr(1, pc.SourceData, Sh.Name) Then _
              pc.Refresh
        End If
    Next
End Sub

The preceding code checks whether the deactivated worksheet is the source of PivotCache objects. If it is, the code refreshes that pivot cache, which updates any pivot tables or charts based on the cache. This approach is more efficient than using the PivotTable object because more than one pivot table may share the same cache.

You could make the code even more efficient by checking if any source data ...