Sort problem - activesheet

tommyleinen

Board Regular
Joined
Aug 22, 2009
Messages
74
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You are missing several worksheet qualifiers:

Rich (BB code):
    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
 
Upvote 0
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:

Rich (BB code):
    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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Notice the difference here?

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

You are using 2 columns instead of one.
 
Upvote 0
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! :laugh:

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
 
Upvote 0

Forum statistics

Threads
1,223,061
Messages
6,169,873
Members
452,287
Latest member
winnievmex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top