Macros running concurrently - need 1 to finish before other begins

[bluefish44]

I have two macros which I have strung together into 1 long macro.

The first macro refreshes a bunch of sheets with Power query.
The second macro populates cells with formulas and drags down the formulas next to the updated queries.
When I run 1 macro, wait for it to finish and then run the second, things work fine.
Problem is, when I combine them into 1 long macr,o the 2nd part begins dragging my formulas down before the queries are completed refreshing.
Is the a way to delay the second part of the macro from starting until the entire refresh of the power query is complete?

Thanks in advance

 

[bluefish44]

For reference - this is the first bit of code I need to completely finish before the second code begins:

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets


ws.Visible = xlSheetVisible


Next ws




'run power query






Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections






lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh




Next cn

 

[JoeMo]

Instead of combining the two macros, have the first call the second just before the End Sub line of the first macro.

 

[bluefish44]

Hi JoeMo - thanks for the response -

I have added Call Macro2 to the bottom of the first macro before the End Sub instead of combining the 2 macros, but the same problem persists.
Macro2 finishes before the query from the first macro is complete.

 

[JoeMo]

Hi JoeMo - thanks for the response -

I have added Call Macro2 to the bottom of the first macro before the End Sub instead of combining the 2 macros, but the same problem persists.
Macro2 finishes before the query from the first macro is complete.

I'm not familiar with the particular type of query you are using so this is just a couple of shots in the dark.

First try adding this line after the cn.Refresh bit

DoEvents

If that doesn't work try:

Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 10) 'change 10 (seconds) to suit

The latter should allow recalculation to occur while pausing the macro.

 

[bluefish44]

Thanks JoeMo -

What I did was add this line of code at the end of the first macro and it did the trick....

Application.OnTime Now() + TimeValue("00:00:30"), "Macro2"