RefreshAll in a particular order

[BakerUK]

Hi all

I have some jobs to run in VB which include tables (updating from external sources) and pivot tables (which link to those tables).

I started with ActiveWorkbook.RefreshAll and it wasn't always updating the pivots, so I changed it to refresh just the table and then the pivots. In some cases that still didn't work, so I added a 5 second delay before the second refresh. In one case that didn't even work!

I've now split out every pivot table to refresh one by one, rather than RefreshAll but it seems very long-winded (I have loads of pivots).

Is there an easier way of writing that?

 

[steve the fish]

Try this:

For Each sh In ThisWorkbook.Sheets
    For Each pt In sh.PivotTables
        pt.PivotCache.Refresh
    Next
Next

 

[FormR]

Hi, here is another option that you can try that, hopefully, also takes care of refreshing the external data sources:

Sub RefreshEmAll()
Dim cn As WorkbookConnection, pc  As PivotCache
For Each cn In ThisWorkbook.Connections
    cn.ODBCConnection.BackgroundQuery = False
    cn.Refresh
Next cn
For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
Next pc
End Sub

 

[BakerUK]

Thanks to both of you, that's a massive help!