| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why this code is not work?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why this code is not work?
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. Error code when select worksheet wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why this code is not work?
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? "Dave Peterson" wrote: 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. Error code when select worksheet wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why this code is not work?
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 Error code when select worksheet wrote: 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? "Dave Peterson" wrote: 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. Error code when select worksheet wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why this code is not work?
Thank you very much. It work now.
"Dave Peterson" wrote: 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 Error code when select worksheet wrote: 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? "Dave Peterson" wrote: 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. Error code when select worksheet wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number Format Code won't work | Excel Discussion (Misc queries) | |||
| Workbook event code won't work on laptop | Excel Worksheet Functions | |||
| searching by number or code ina work book | Excel Discussion (Misc queries) | |||
| code is not to work on sheet1 | New Users to Excel | |||
| Code does not work on emailed file | Excel Discussion (Misc queries) | |||
Hybrid Mode
