Refresh Excel Pivot Tables in Background

[hotbread]

Hi,

I have a macro that tries to refresh pivot caches that have their BackgroundQuery property set to True. The reason for refreshing them in the background is because I want other code to run while the pivot caches are being refreshed (they take a long time).

The first pivot cache begins to refresh, and the round revolving world symbol appears at the bottom of the window, but never completes refreshing the first pivot cache. If I double click the world symbol, it says "All rows fetched", but doesn't move on to refresh the next pivot table. If I step through the code (F8), it works perfectly, but whenever I let the code run on its own, it never completes refreshing the first cache.

My 2 coding attempts are as follows:

Sub RefreshPivotCaches1()
    Dim sh As Worksheet, pv As PivotTable
    
    For Each sh In ThisWorkbook.Sheets
        For Each pv In sh.PivotTables
            On Error Resume Next ' refreshing the first pivot cache results in an error, even though the pivot cache starts to refresh
            pv.PivotCache.Refresh
            DoEvents ' Without this the refresh doesn't begin
            { code loop goes here }

        Next pv
    Next sh
End Sub

Sub RefreshPivotCaches2()

    ThisWorkbook.RefreshAll
    DoEvents
    { code loop goes here }

End Sub

In both cases, the code between the curly brackets involves various actions that take about 3 minutes, the time it normally takes to refresh all pivot caches in the workbook.

Does anyone know how I can use VBA to refresh all the pivot caches in the background and run other code at the same time?

Tim

 

[SkipVought]

What happens if you move the DoEvents statement in the loop, BEFORE the call to refresh?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hotbread]

Hi Skip,

I've tried putting the DoEvents statement in every position I can think of, but the same problem persists - it looks successful as the first cache begins to refresh, but it never completes refreshing the first cache and doesn't move to the next unless I break the code.