error on sorting a range
 

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

error on sorting a range
 

On Sat, 14 Nov 2009 03:27:01 -0800, RocketRod
wrote:

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

error on sorting a range
 

Thank you - that worked

"Lars-Ã…ke Aspelin" wrote:

On Sat, 14 Nov 2009 03:27:01 -0800, RocketRod
wrote:

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
.