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