Why this code is not work?

  • Thread starter Thread starter Error code when select worksheet
  • Start date Start date
E

Error code when select worksheet

I want to sort other worksheet in the same workbook,
I try to run macro from Sheet2:
-----------------------------------
Sheets("Sheet1").Select
Cells.Select
....
--------------------------------
it work find.
But when I create one button in Sheet2 and put these code under that button.
After I click (run) that button, it give me an error:

Run-time error '1004'
Select method of range class failled

Pls can someone help me?
Thanks
 
If you qualify the ranges, it'll work:

Sheets("sheet1").select
Sheets("sheet1").cells.select

An unqualified range in a General module will refer to the activesheet.

An unqualified range behind a worksheet will refer to that worksheet that owns
the code.

And you can only select a range on the activesheet.

So your code (behind sheet2) is equivalent to:

Sheets("sheet1").select 'Sheet1 is the activesheet
Sheets("sheet2").cells.select 'and the .select fails since sheet2 is not the
activesheet.
 
Thank you. That error id passed, and when I add the following:

Selection.Sort key1:=Range("D2"), order1:=xlAscending, key2:=Range("B2") _
, order2:=xlAscending, header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal

I got an error:
Run time error 1004
The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first sort by box isn't the same or blank.


But this code I run in Macro is working fine.
Can you tell where is the mistake?
 
All those range()'s are unqualified.

With worksheets("Sheet1")
.select
.cells.select
selection.sort key1:=.range("d2"), ...
key2:=.range("b2"), ....

....

Those dots in front of the range()'s mean they refer to the object in the
previous With statement.

You could also do:

Selection.Sort key1:=sheets("sheet1").Range("D2"), order1:=xlAscending, _
key2:=sheets("sheet1").Range("B2") _
, order2:=xlAscending, header:=xlYes, OrderCustom:=1, _
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
 
Thank you very much. It work now.

Dave Peterson said:
All those range()'s are unqualified.

With worksheets("Sheet1")
.select
.cells.select
selection.sort key1:=.range("d2"), ...
key2:=.range("b2"), ....

....

Those dots in front of the range()'s mean they refer to the object in the
previous With statement.

You could also do:

Selection.Sort key1:=sheets("sheet1").Range("D2"), order1:=xlAscending, _
key2:=sheets("sheet1").Range("B2") _
, order2:=xlAscending, header:=xlYes, OrderCustom:=1, _
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Click to expand...
 
Back
Top