Refresh Data and Pivot Tables in Excel Using VBA

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