Writing a Sort Macro
I have the following code that I'm trying to use to sort:
Sheets("OPEN").Select Selection.Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal It errors out with the error: 'Runtime error "1004" Application-defined or Object-defined error' |
Writing a Sort Macro
EttsiaM, You also have to select the sort range on the Open sheet. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Error trying to sort in a Macro" wrote in message I have the following code that I'm trying to use to sort: Sheets("OPEN").Select Selection.Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal It errors out with the error: 'Runtime error "1004" Application-defined or Object-defined error' |
Writing a Sort Macro
Thanks so far....
I added the range to the front of the sort: Sheets("OPEN").Select Range("A:BZ").Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal Now, it doesn't error out. It doesn't sort either. Do I need to specify on which sheet the range is? I tried using the Sheet("Open").Range(.... command, but that errors out with the same application error. Thanks for your help. ---Ken "Jim Cone" wrote: EttsiaM, You also have to select the sort range on the Open sheet. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Error trying to sort in a Macro" wrote in message I have the following code that I'm trying to use to sort: Sheets("OPEN").Select Selection.Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal It errors out with the error: 'Runtime error "1004" Application-defined or Object-defined error' |
Writing a Sort Macro
You are welcome so far. <g
The code should be in a standard module, not a module behind a sheet. "Range" in a sheet module refers to that sheet, not the active sheet. Also, the key columns should have some sortable data in them. It is good practice to qualify all range callouts with the sheet name... (note the dot in front of range in four places. '-- With Sheets("OPEN") ..Range("A:BZ").Sort Key1:=.Range("Y2"), Order1:=xlAscending, _ Key2:=.Range("AE2"), Order2:=xlAscending, _ Key3:=.Range("AB2"), Order3:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With '-- And you do not have to select the sheet in order to sort it. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Error trying to sort in a Macro" wrote in message Thanks so far.... I added the range to the front of the sort: Sheets("OPEN").Select Range("A:BZ").Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= xlSortNormal Now, it doesn't error out. It doesn't sort either. Do I need to specify on which sheet the range is? I tried using the Sheet("Open").Range(.... command, but that errors out with the same application error. Thanks for your help. ---Ken "Jim Cone" wrote: EttsiaM, You also have to select the sort range on the Open sheet. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Error trying to sort in a Macro" wrote in message I have the following code that I'm trying to use to sort: Sheets("OPEN").Select Selection.Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal It errors out with the error: 'Runtime error "1004" Application-defined or Object-defined error' |
Writing a Sort Macro
Yahtzee!
Thanks. "Jim Cone" wrote: You are welcome so far. <g The code should be in a standard module, not a module behind a sheet. "Range" in a sheet module refers to that sheet, not the active sheet. Also, the key columns should have some sortable data in them. It is good practice to qualify all range callouts with the sheet name... (note the dot in front of range in four places. '-- With Sheets("OPEN") ..Range("A:BZ").Sort Key1:=.Range("Y2"), Order1:=xlAscending, _ Key2:=.Range("AE2"), Order2:=xlAscending, _ Key3:=.Range("AB2"), Order3:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With '-- And you do not have to select the sheet in order to sort it. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Error trying to sort in a Macro" wrote in message Thanks so far.... I added the range to the front of the sort: Sheets("OPEN").Select Range("A:BZ").Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= xlSortNormal Now, it doesn't error out. It doesn't sort either. Do I need to specify on which sheet the range is? I tried using the Sheet("Open").Range(.... command, but that errors out with the same application error. Thanks for your help. ---Ken "Jim Cone" wrote: EttsiaM, You also have to select the sort range on the Open sheet. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Error trying to sort in a Macro" wrote in message I have the following code that I'm trying to use to sort: Sheets("OPEN").Select Selection.Sort Key1:=Range("Y2"), Order1:=xlAscending, Key2:=Range("AE2") _ , Order2:=xlAscending, Key3:=Range("AB2"), Order3:=xlAscending, Header _ :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _ , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ xlSortNormal It errors out with the error: 'Runtime error "1004" Application-defined or Object-defined error' |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com