Long Macro Running too Fast actually Pastes "#GETTING_DATA"

[phelbin]

I've got a weird problem. I'm running ActiveWorkbook.RefreshAll to get data from SQL via PowerQuery, and then I have a long macro to do some refreshing within the workbook.

When I run them separately, everything is great. But I run into trouble with I try to run one after the other.

Specifically, there's one data set that I refresh and then copy paste special, but the macro is running slow and so it's actually pasting "#GETTING_DATA"

I've tried Application.Wait in a few different places. I've also tried enabling screen updating. And nothing works.

Someone suggested disabling background refresh in the connections properties. Mine is grayed out, but it seems to already be disabled.

So...any ideas?

And I hope that makes sense.

 

[tonyyy]

You might try adding DoEvents after the RefreshAll function...

"DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue..."

Cheers,

tonyyy

 

[phelbin]

Thanks, Tonyyy.

I tried DoEvents and there's no change.

I also tried to do the refresh on Workbook_Open, and I also tried using a message box after ActiveWorkbook.RefreshAll to trigger the other macros, and still no change.

I'm about to admit defeat. Any other ideas?

 

[phelbin]

Ok...this might not make a lot of sense without context, but here is the code:

Private Sub Workbook_Open()


    ActiveWorkbook.RefreshAll
    
    DoEvents
    
    Call Refresh


End Sub

Sub Refresh()


 Application.ScreenUpdating = False




    
'--> This makes the list of GL's dynamic based on the hidden pivot table.


    Sheets("PivotTable").Visible = True


    Lastrow = Sheets("PivotTable").Cells(Rows.Count, 5).End(xlUp).Row


    Sheets("PivotTable").Select
    
    Range("C5").Select
    Selection.Formula = "=IFNA(VLOOKUP(A5,E5:F" & Lastrow & ",2,FALSE),"""")"
    Selection.AutoFill Destination:=Range("C5:C" & Lastrow & "")
    
    Sheets("PivotTable").Visible = False




'--> Populate Forecast Tool using data on hidden pivot table


    Range("B5").Formula = "=CUBEMEMBER(""ThisWorkbookDataModel"",""[Query1].[GLMain_Code].&[""&LEFT(PivotTable!A5,5)&""]"")"
    Range("C5").Formula = "=CUBEMEMBER(""ThisWorkbookDataModel"",""[Query1].[GLDept_Code].&[""&MID(PivotTable!A5,7,2)&""]"")"
    Range("D5").Formula = "=CUBEMEMBER(""ThisWorkbookDataModel"",""[Query1].[GLYear_Code].&[""&MID(PivotTable!$A5,10,3)&""]"")"
    Range("E5").Formula = "=CUBEMEMBER(""ThisWorkbookDataModel"",""[Query1].[GLProject_Code].&[""&RIGHT(PivotTable!$A5,4)&""]"")"
    Range("F5").Formula = "=PivotTable!C5"
    


'--> This is the section with the data that's causing problems on the very last step below


    Range("L5").Select
    Selection.Formula = "=SUMPRODUCT((('Last Activity'!$A$1:$A$5000)=$K5)*('Last Activity'!B$1:B$5000))"
    Selection.Copy
    Range("M5:W5").Select
    ActiveSheet.Paste 
    


    Rows("6:6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp


    Range("A5:Z5").Copy
    Range("A5:Z" & Lastrow & "").Select
    ActiveSheet.Paste
    
    Range("A5:F5").Copy


    Sheets("YTD").Select
    Range("A5:F5").Select
    ActiveSheet.Paste
    


'--> Populate YTD using data on hidden pivot table
        
    Sheets("YTD").AutoFilter.Sort.SortFields.Clear


    Rows("6:6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp


    Range("A5:Z5").Copy
    Range("A5:Z" & Lastrow & "").Select
    ActiveSheet.Paste






'--> This is where the problem is.  The cells all say "#GETTING_DATA" but the macro finishes the next few lines of code before it can actually get the data.  So it copies and pasts special the test "#GETTING_DATA"


    Sheets("Forecast Tool").Select
    
    Range("L5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False  




End Sub

Thanks for your help!! I hope that makes sense.

 

[tonyyy]

You might try...

ActiveWorkbook.RefreshAll
Do While Application.CalculationState <> xlDone
    DoEvents
Loop

Or...

ActiveWorkbook.RefreshAll
Do While Application.CalculateUntilAsyncQueriesDone
    DoEvents
Loop

 

[tonyyy]

Sorry, the second option should read...

ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

 

[phelbin]

THE SECOND WORKED!!!! Tonyyy, you are awesome!!

 

[tonyyy]

Glad that worked out, phelbin...