error question

[JT]

I'm running an Excel macro from an Outlook module. I can automate this
process when a reminder kicks off. The macro has been working until this
week.

This is my variable: Public xlApp As Excel.Application

I create a new Excel application at the beginning of the macro:

Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Workbooks.Add

I then do some stuff and I now want to select the data and sort it

The next line works and the appropriate data is selected

xlApp.Range("A2:Y2").Select

However, when the next line is run, it kicks me out of the Outlook module

xlApp.Range(Selection, Selection.End(xlDown)).Select

Any ideas why this is happening? It had been working fine for several weeks
but now it won't finish. Any suggestions would be appreciated. Thanks for
the help...

 

[Joel]

I suspect the active worksheet has changed. In the following line you are
referencing a workbook and a range without the sheet.

from
xlApp.Range("A2:Y2").Select
to
xlApp.sheets("Sheet1").Range("A2:Y2").Select

---------------------------------------------

I don't like the select method you are using. You don't have to select.

from
xlApp.Range(Selection, Selection.End(xlDown)).Select
to
with xlApp.Sheets("Sheet1")
set FirstCell = .Range("A2")
LastRow = FirstCell.end(xldown).Row
Set LastCell = .Range("Y" & LastRow)
Set DataRange = .Range(Firstcell,LastCell)
end with