Macro for refresh PivotTable not working

Posted by Ken on November 14, 2001 7:21 AM

I need a macro that will refresh all PivotTables within a workbook when the macro button is pressed. Juan Pablo kindly gave me this macro, however it is hanging up and I don't know enough about VBA to correct it.

Sub RefereshAll()
Dim Sh as Worksheet
Dim Pt as PivotTable

For each Sh in Sheets
For each Pt in Sh
Pt.Refresh
Next Pt
Next Sh

End Sub

Thank you very much
Ken

Posted by Juan Pablo on November 14, 2001 7:26 AM

Are you getting any errors ? I didn't test it, just thought that should work (NT)

Posted by Juan Pablo on November 14, 2001 9:29 AM

Ok, tested, corrected, try this one...

Sub RefereshAll()
Dim Sh As Worksheet
Dim Pt As PivotTable

For Each Sh In Sheets
For Each Pt In Sh.PivotTables
Pt.RefreshTable
Next Pt
Next Sh

End Sub

It worked with some dummy data i set in a couple of worksheets in one workbook.

Juan Pablo

Posted by Ken on November 14, 2001 10:04 AM

Thanks Juan Pablo, it works great I appreciate it.