I have a macro in an Access database that opens a new Excel workbook and
writes data to a sheet in the workbook. The macro then does "stuff" to the
data (sort, total, etc.)

I'm receiving an error message when the macro sorts. The message I get is:
"Method 'Range' of object_Global' Failed".

Following is the code used to sort the data:

..Range("A1").Select
..Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select
..Range(.Selection, .Selection.End(xlDown)).Select

**error occurs on the following line:

..Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _:=xlSortNormal

..Range("A1").Select

I don't get this error all of the time. I run the macro and it works. I
close Excel, run the macro again and get the error message. I click the
"end" button. If I re-run the macro, it works again. Is there anything I
can change or add to the code so I don't get this error?

Thanks for the help and suggestions..........



--
JT

Your range references for the Key properties are not preceded by a period,
so they are unqualified and refer to the activesheet. If the activesheet
isn't the sheet being sorted, then you would get that error.

--
Regards,
Tom Ogilvy

"JT" wrote in message
...
I have a macro in an Access database that opens a new Excel workbook and
writes data to a sheet in the workbook. The macro then does "stuff" to the
data (sort, total, etc.)

I'm receiving an error message when the macro sorts. The message I get is:
"Method 'Range' of object_Global' Failed".

Following is the code used to sort the data:

.Range("A1").Select
.Range(.ActiveCell, .ActiveCell.Offset(0, 2)).Select
.Range(.Selection, .Selection.End(xlDown)).Select

**error occurs on the following line:

.Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2")
_
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _:=xlSortNormal

.Range("A1").Select

I don't get this error all of the time. I run the macro and it works. I
close Excel, run the macro again and get the error message. I click the
"end" button. If I re-run the macro, it works again. Is there anything I
can change or add to the code so I don't get this error?

Thanks for the help and suggestions..........



--
JT

Hi JT,

I'm not sure but try with the dot....

Selection.Sort Key1:=.Range("B2")...Key2:=.Range("C2") _

let me know,
regards,
ste