Macros running concurrently - need 1 to finish before other begins

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
185
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
185
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,363
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Instead of combining the two macros, have the first call the second just before the End Sub line of the first macro.
 

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
185
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,363
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Apr 3, 2009
Messages
185
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,119,104
Messages
5,576,140
Members
412,700
Latest member
IIII
Top