You probably need to qualify the reference in the Key argument to
sort.

Change
Key1:=Range("B2"), Order1:=xlAscending
' to
Key1:=Sheets("Sheet1").Range("B2"), Order1:=xlAscending


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"BL" wrote in message
...
Hi, John,

Thank you very much for you advice. Unfortunately, my problem
is not yet
solved. The user menu I use is not a class module but a
standard worksheet
with some botton command only. I try to replicate the problem
in other
simple worksheet. If I have only one sheet, I can write the
code in the
standard module as

Sub Test()

ActiveSheet.Range(Cells(2, 2), Cells(6, 2)).Sort _
Key1:=Range("B2"), Order1:=xlAscending
End Sub

However, If I have more than one sheet, the following code is
not working

Sub Test2()
Sheets("Sheet1").Range(Cells(2, 2), Cells(6, 2)).Sort _
Key1:=Range("B2"), Order1:=xlAscending
End Sub

I cannot figure out the reason. Please help.

Regards,

BL

"John Green" wrote:

Don't place the code in Test2 in the code module behind the
worksheet (which
is a class module). Insert a standard module using the VB
editor and place
the code there.

When you use references such as Cells in the class module
behind a sheet, it
refers to the cells in that sheet, not the active sheet.

Alternatively, you could make your references more explicit:

Sub Test2()
Sheets("Data").Select
ActiveSheet.Range(ActiveSheet.Cells(1, 1),
ActiveSheet.Cells(19,
10)).Select
Selection.Sort key1:=ActiveSheet.Range("F1")
End Sub

or

Sub Test2()
Sheets("Data").Select
With ActiveSheet
.Range(.Cells(1, 1), .Cells(19, 10)).Select
Selection.Sort key1:=.Range("F1")
End With
End Sub

Even better, don't select anything:

Sub Test2()
With Sheets("Data")
.Range(.Cells(1, 1), .Cells(19, 10)).Sort
key1:=.Range("F1")
End With
End Sub

John Green - Excel MVP



"BL" wrote in message
...
Dear all,

I am puzzled in the following situtations. There are 2
worksheets in the
workbook, one for user menu and the other is the data. I
would like to do
some sorting in the data. As a result, I write down the
following in the
"UserMenu" worksheet.

Sub Test2()
Sheets("Data").Select
ActiveSheet.Range(Cells(Firstrow, 1), Cells(Lastrow,
19)).Select
Selection.Sort key1:=Range("R10")
End Sub

However, the coding got the error message "Run-time error
1004 -
Application-defined or Object-defined error"

However, if I paste the code into the "Data" sheet as
follows :

Sub Test2()
ActiveSheet.Range(Cells(Firstrow, 1), Cells(Lastrow,
19)).Select
Selection.Sort key1:=Range("R10")
End Sub

The code can be executed without any problem. What is the
reason for the
problem and your help will be much appreciated.

BL