The following Excel 2003 VBA macro can be used to refresh external data lists and the associated pivot tables within a workbook programmatically. This procedure is useful to provide users with an ability to generate Excel reports as needed with the latest data. Later versions of Excel can simply use ThisWorkbook.RefreshAll
.
Sub refresh_data() Dim pt As PivotTable Dim ws As Worksheet Dim pc As PivotCache Dim lo As ListObject 'Change setting in each Pivot Table to remove stale or missing items For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next pt Next ws 'Refresh each Pivot Cache to clear stale entries For Each pc In ActiveWorkbook.PivotCaches On Error Resume Next pc.Refresh Next pc 'Refresh each List Object to retrieve latest external data For Each ws In ActiveWorkbook.Worksheets For Each lo In ws.ListObjects lo.Refresh Next Next 'Refresh all Pivot Tables For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable Next pt Next ws End Sub