Sorts using a Macro

[sharpeye]

Hi guys, Im looking for a little help with my spreadsheet.
What im trying to do is a sort using row K largest to smallest then row I largest to smallest, however, the selection of rows is different each time I do the sort. I tried to record a macro after highlighting my selection but it just sorts the same selection every time.

I would like to select rows 52 to 97, CNTL + SHIFT + S, sorted by K then I
Then it may be select rows 105 to 142, CNTL + SHIFT + S, sorted by K then I

and so on.

Any ideas??
Many thanks

 

[James006]

Hi,

Why don't you post your initial macro ... to allow for the necessary modification ...

 

[sharpeye]

Yeah sorry, this is what the macro looks like. I can see the selection probably just wants taking out of the macro now ive seen the code

Sub Sort()
'
' Sort Macro
' Sort K then I
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWorkbook.Worksheets("SDLRES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SDLRES").Sort.SortFields.Add Key:=Range( _
"K2858:K2952"), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("SDLRES").Sort.SortFields.Add Key:=Range( _
"I2858:I2952"), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("SDLRES").Sort
.SetRange Range("A2858:IJ2952")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[sharpeye]

After a little digging around on the internet I found I should replace "Range" with "Selection" but when I try to run the Macro I get

Compile Error, Expected End With

 

[sharpeye]

Actually thats not correct, I must have made another change so I changed it all back and started again. Now when I replace "Range" with "Selection" it just does nothing. Here's my Macro now

Sub Sort()
'
' SortD Macro
' Sort
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWorkbook.Worksheets("SDLRESTG").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SDLRESTG").Sort.SortFields.Add Key:=Selection, SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("SDLRESTG").Sort.SortFields.Add Key:=Selection, SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("SDLRESTG").Sort
.SetRange Selection
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[MARK858]

Post your code as you now have it.
Edit: OP just has.... reading it.

sharpeye, your key should be the column (or first cell of) the column you are sorting on.

 

[sharpeye]

This is where I am now

Sub Sort()
'
' SortD Macro
' Sort
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWorkbook.Worksheets("SDLRESTG").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SDLRESTG").Sort.SortFields.Add Key:=Selection.Columns(11), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("SDLRESTG").Sort.SortFields.Add Key:=Selection.Columns(9), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("SDLRESTG").Sort
.SetRange Selection
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

This does nothing at all (as you may be able to tell, VBA isnt my strong suit)

 

[James006]

Hello,

As pointed out by Mark ... you need to adjust your Sort key ...

[COLOR=#333333]Key:=Selection.Columns(11)[/COLOR]

1. Get rid of the word Selection

2. Make sure Columns(11) which is Column K is the Column you want to use to sort your data ...

Hope this will help

 

[sharpeye]

Dont know how but I managed to get it all working (its not so much that I dont know how I got it working but more the how it wasnt working thats puzzling??)
Thanks for your help =)

 

[James006]

You are welcome ...

Glad you could fix your problem ...