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

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

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

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

By default, workbooks do not contain any standard modules. Standard modules
can only be created by you (Insert||Module in the VB Editor window).

The modules supplied automatically by Excel that sit behind each worksheet
and the ThisWorkbook module are all CLASS modules. One characteristic of a
class module is that it can contain event procedures that respond to events
such as clicking a button.

Normally I would insert a standard module to contain procedures like your
Test procedure, which would have then worked as you expected when called
from your button's event procedure.

However, I also pointed out that you can place Test in a class module but
you must then take account of the special characteristics of a class module
where unqualified references such as Cells(1,2) refer to the cells in the
sheet associated with the class module, not the active sheet and
Range("R10") refers to R10 in the associated sheet, not the active sheet.
Therefore, you need to make your references more explicit. For example,
ActiveSheet.Cells(1,2) and Sheet("Data").Range("R10") as I did in the
examples I gave you.

John Green - Excel MVP

"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