VBA sort with manual selection not working

[sharpeye]

Hi everyone, I need some help trying to figure out why I cant get a sort macro to work.
I have the macro working perfectly on one worksheet but im trying to implement the same macro on a different worksheet and I cant get it working at all
This is the VBA I get when I record the macro

Sub BSORT()
'
' BSORT Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Rows("106:124").Select
ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
"K106:K124"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
"I106:I124"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
"H106:H124"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
"D106:D124"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("RES-B").Sort
.SetRange Range("A106:CO124")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

This is not how I want the marco to work as this uses the cells that I highlighted but I want to sort a user defined range instead. The VBA for the marco that does work how I would like is this

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

Sub BSORT()
'
' BSORT Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Rows("106:124").Select
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
        "K106:K124"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
        "I106:I124"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
        "H106:H124"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add2 Key:=Range( _
        "D106:D124"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("RES-B").Sort
        .SetRange Range("A106:CO124")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This is not how I want the marco to work as this uses the cells that I highlighted but I want to sort a user defined range instead. The VBA for the marco that does work how I would like is this

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

When I try to make the changes in the first VBA to match this second one though I get a runtime error '40036' Application-defined or Object-defined error and when I check the debug, this line is what it seems to not like, highlighted in yellow

ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Clear

Whats even more puzzling is I have used the exact same VBA on another seperate worksheet using a different trigger character and it works perfectly as well.

Any ideas??

 

[sharpeye]

Its ok I've fixed it

 

[Peter_SSs]

Its ok I've fixed it

Glad you have it sorted. Thanks for letting us know.

A couple of things for the future though:

1. When posting vba code in the forum, please use the available code tags. My signature block below has more details.
   Compare how easy/hard the code is to read in the top part of post #1 with the bottom half where I have repeated your code but used the vba code tags.
   Much easier for helpers to read &/or debug.
   
   
2. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

One further thing:

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[sharpeye]

This is the code that works

Sub Sort()
'
' Sort Macro
' SORT
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Selection.Select
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add Key:=Selection.Columns(11), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add Key:=Selection.Columns(9), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add Key:=Selection.Columns(8), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("RES-B").Sort.SortFields.Add Key:=Selection.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("RES-B").Sort
        .SetRange Selection
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Peter_SSs]

This is the code that works