Refreshes all pivot tables in VBA

[schappelljr]

I have built a spreadsheet with 24 pivot tables. I trying to write a Macro that will refreshes the all of the pivot tables. I am doing them 1 at a time in VBA. But is there a refresh all command in VBA? Right now it is taking the PC too long.


this is what i have so far


ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable10").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable12").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable13").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable14").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable15").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable16").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable17").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable18").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable27").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable19").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable20").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable21").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable29").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable30").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable31").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable32").PivotCache.Refresh
Sheets("Mcap Data").Select
Range("A2:C2").Select
End Sub

 

[JSA715]

Here's a little nugget from OZGrid

Sub AllWorkbookPivots()
 
Dim pt As PivotTable
Dim ws As Worksheet
 
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
                    pt.RefreshTable
        Next pt
    Next ws
 
[B]End Sub[/B]

http://www.ozgrid.com/VBA/pivot-table-refresh.htm

 

[Norie]

How about the crazily named RefreshAll method?

Should refresh everything but I don't know if it'll make any difference to the performance.

ThisWorkbook.RefreshAll

 

[JSA715]

you and your one-liners, Norie...

 

[schappelljr]

The one-line works the best. It took 14 sec the old way and 1 sec the new way.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks