CODEWITHSUNDEEP
vbaexcel
Matteo Busetti
Matteo Busetti

Reputation: 1

Wait until application.run is completed

I'm trying to write a macro on one file that opens another workbook, runs a macro to get some data, copies the data into the first workbook and closes the second workbook.

I have however encountered a problem because it seems like the selection of the range is executed while the macro in the second workbook is still running and therefore selects the entire column:

i.e. the ws2.Range(StartCell, ws2.Cells(LastRow, "A")).Select string in the code below selects the whole column, and not just the cells where there is data.

'run macro in IB API file to get portfolio data
Application.Run "TwsDde.xls!Sheet15.subscribeToPorts"

'select data in column A from IB API file
Dim LastRow As Long
Dim StartCell As Range
Set StartCell = Range("A8")
LastRow = ws2.Cells(ws2.Rows.Count, StartCell.Column).End(xlDown).Row
ws2.Range(StartCell, ws2.Cells(LastRow, "A")).Select

Has anyone encountered this problem before or has any ideas on how to solve it?

Upvotes: 0

Views: 1052

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

Your calculation of LastRow is wrong. It should be xlUp instead of xlDown.

Upvotes: 2

Related Questions