Writing a Sort Macro

  • Thread starter Thread starter Error trying to sort in a Macro
  • Start date Start date
E

Error trying to sort in a 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'
 
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'
 
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
 
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
 
Yahtzee!

Thanks.

Jim Cone said:
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
Click to expand...
 
Back
Top