'/-------------------------------------/
Sub PTRefresh()
'Refresh all pivot tables in this workbook one at a time
'
Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, 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
'/-------------------------------------/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Lou Sanderson" wrote:

Windows XP, Excel 2002 SP 3

I have a simple macro that I run to refreshs all my external datasources in
my spreadsheet. Recently I've added some pivot tables as well. I would like
my macro to refresh the pivot tables if possible - currently it does not. Any
suggestions?

Here is my macro

sub macro1()

application.displayalerts = false
sheets("sheet1").select
range("a1").select
activeworkbook.refreshall
application.displayalerts = true
msgbox("refresh complete")
end sub

Thanks,
Lou Sanderson