Sort problem - activesheet

[tommyleinen]

Hi there, I wonder if anyone can help, I have part of a macro getting run-time error '1004', trying to sort columns J:K in a workbook with multiple sheets of similar format.

Here is the code that gets stuck at ".Apply":

Columns("J:K").Select
Application.CutCopyMode = False
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"J1:K9500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("J1:K9500")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select



I am using .Activesheet, because the macro is to be able to be used seperately on different sheets, other wise I will need to have a seperate macro per sheet, which I don't want to have to do - makes it easier for changing the macro in future.

Anyone any ideas? Thanks in advance.

 

[Andrew Poulsom]

You are missing several worksheet qualifiers:

    ActiveSheet.Columns("J:K").Select
    Application.CutCopyMode = False
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=ActiveSheet.Range( _
        "J1:K9500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveSheet.Range("J1:K9500")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("A1").Select

 

[tommyleinen]

Hi Andrew, thanks, though I am still having the same problem...

Full error msg:
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.

I have a feeling it is to do with not specifying the name of the sheet, though can't think of another way around it.

You are missing several worksheet qualifiers:

    ActiveSheet.Columns("J:K").Select
    Application.CutCopyMode = False
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=ActiveSheet.Range( _
        "J1:K9500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveSheet.Range("J1:K9500")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("A1").Select

 

[Andrew Poulsom]

What's assigned to Key is the whole sort range. It should be a single field. The macro recorder will help you with the syntax.

 

[tommyleinen]

I actually used the macro recorder to get the syntax and just changed the name of the sheet referenced and expanded the range, eg:

Columns("J:K").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J1:J13" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("J:K")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N3").Select
End Sub

 

[Andrew Poulsom]

Notice the difference here?

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J1:J13" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

You are using 2 columns instead of one.

 

[tommyleinen]

Well spotted, thankyou! For some reason, even when highlighting the area I wish to sort, the code defaults back to the first column (when using record macro)... to fix , I added the second column in the sort process and manually changed the range later as pointed out above.

Thanks for your help, it's been driving me nuts!

Here is my final code for completeness:

Range("J1:K9500").Select


ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("J1:J9500") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("K1:K9500") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("J1:K9500")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With