error on sorting a range

[RocketRod]

Hi
I have an error happening when I run this Sort on a named range called
"DataRange" on the worksheet tab "Claim"

The message s
"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"

The code is

Application.Goto Reference:="DataRange"
ActiveWorkbook.Worksheets("Claim").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Claim").Sort.SortFields.Add
Key:=Range("DataRange"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Claim").Sort
.SetRange Range("DataRange")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The Debug highlights the .Apply line.
When I step through the program the range is selected OK.
I recorded the code by selecting Goto button and selecting DataRange but
changed the cell reference from the B3:J60 notation to DataRange in the macro.


Correction suggestions please

 

[Lars-Åke Aspelin]

Hi
I have an error happening when I run this Sort on a named range called
"DataRange" on the worksheet tab "Claim"

The message s
"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"

The code is

Application.Goto Reference:="DataRange"
ActiveWorkbook.Worksheets("Claim").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Claim").Sort.SortFields.Add
Key:=Range("DataRange"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Claim").Sort
.SetRange Range("DataRange")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The Debug highlights the .Apply line.
When I step through the program the range is selected OK.
I recorded the code by selecting Goto button and selecting DataRange but
changed the cell reference from the B3:J60 notation to DataRange in the macro.


Correction suggestions please

Try changing
Key:=Range("DataRange")
to
Key:=Range("DataRange")(,n)
where n is the number of the column, within the range that you want
the range sorted by,

Key:=Range("DataRange")(,1)
if you want the data to be sort by the first column, column B in your
example.

Hope this helps / Lars-Åke

 

[RocketRod]

Thank you - that worked

Try changing
Key:=Range("DataRange")
to
Key:=Range("DataRange")(,n)
where n is the number of the column, within the range that you want
the range sorted by,

Key:=Range("DataRange")(,1)
if you want the data to be sort by the first column, column B in your
example.

Hope this helps / Lars-Ã…ke
.