Here's a macro for refreshing all pivot tables in a workbook. Hope this
helps.
Gary Brown
Sub PTRefresh()
'Refresh all pivot tables on this worksheet one at a time
'
Dim iSheets As Long, x As Long
Dim iPivot As Long, strCurrentSheet As String
On Error GoTo Exit_PTRefresh
'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count
'remember current sheet
strCurrentSheet = ActiveSheet.Name
If Windows.Count = 0 Then GoTo Exit_PTRefresh
For x = 1 To iSheets
'go to a worksheet to refresh pivot tables
Sheets(x).Activate
'turn warning messages off
Application.DisplayAlerts = False
'refresh all pivot tables on this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iPivot).RefreshTable
Next
'turn warning messages on
Application.DisplayAlerts = True
Next
'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet).Activate
Exit_PTRefresh:
Application.DisplayAlerts = True
End Sub