Refreshing all Pivot Tables in the On_Open() code

[Guest]

Hello
I have 50 pivot tables( 1 original and 49 copies) that need to be refreshed
when I open the workbook. Is there a way to write a For Each...Next procedure
to step through all the pivot tables on a given sheet?
Thanks!

 

[Guest]

Tony,

You can set a flag to make each pivottable (cache) update on open. Here is
some code to set the flag

For Each pc In ActiveWorkbook.PivotCaches
pc.RefreshOnFileOpen = True
Next

or if you want to do it each time the sheet opens via code


Sub Macro1()


Dim ptblcache As PivotCache

For Each ptblcache In ThisWorkbook.PivotCaches
ptblcache.Refresh
Next ptblcache

Set ptblcache = Nothing

End Sub

Best of luck,

Stewart Rogers