Run a macro on a range that is selected

[MrOllyR]

I'm struggling with a sort function.
I would like to select a range, and with a macro attached to a button, sort only the range which I have selected. This obviously has something to do with Range("A1:H15").Select, I'm just not sure how to translate this so it applies only to a range which is currently selected. The columns are set and it always needs to apply to H, G, F, E, and C in that order.

Can anyone point me in the right direction?

Range("A1:H15").Select
ActiveWorkbook.Worksheets("Data (2)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data (2)").Sort.SortFields.Add2 Key:=Range( _
"H2:H15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data (2)").Sort.SortFields.Add2 Key:=Range( _
"G2:G15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data (2)").Sort.SortFields.Add2 Key:=Range( _
"F2:F15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data (2)").Sort.SortFields.Add2 Key:=Range( _
"E2:E15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data (2)").Sort.SortFields.Add2 Key:=Range( _
"C2:C15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data (2)").Sort
.SetRange Range("A1:H15")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[rlv01]

I'm making a lot of guesses about what you want, but maybe something like this.

Private Sub CommandButton1_Click()
    Call SelectionSort
End Sub

Sub SelectionSort()
    Dim SortRange As Range
    
    Set SortRange = Selection
    
    With SortRange
        If .Range("A1").Column <> 1 Or .Columns.Count < 8 Then
            MsgBox "Selected cells: " & SortRange.Address(0, 0) & vbCr & vbCr & "Selection must include cells in columns A - H", vbOKOnly, Application.Name
            Exit Sub
        End If
    End With
    
    If SortRange.Rows.Count >= 2 Then
        With ActiveWorkbook.Worksheets("Data (2)").Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange SortRange
            
            If SortRange.Range("A1").Row = 1 Then
                .Header = xlYes
            Else
                .Header = xlNo
            End If
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Else
        MsgBox "Please select more than one row", vbOKOnly, Application.Name
    End If
End Sub

For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[MrOllyR]

I'm making a lot of guesses about what you want, but maybe something like this.

Private Sub CommandButton1_Click()
    Call SelectionSort
End Sub

Sub SelectionSort()
    Dim SortRange As Range
   
    Set SortRange = Selection
   
    With SortRange
        If .Range("A1").Column <> 1 Or .Columns.Count < 8 Then
            MsgBox "Selected cells: " & SortRange.Address(0, 0) & vbCr & vbCr & "Selection must include cells in columns A - H", vbOKOnly, Application.Name
            Exit Sub
        End If
    End With
   
    If SortRange.Rows.Count >= 2 Then
        With ActiveWorkbook.Worksheets("Data (2)").Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add2 Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange SortRange
           
            If SortRange.Range("A1").Row = 1 Then
                .Header = xlYes
            Else
                .Header = xlNo
            End If
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Else
        MsgBox "Please select more than one row", vbOKOnly, Application.Name
    End If
End Sub

For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Thank you for your help. Apologies for the delay- I wanted to incorporate it into my data just in case there were problems. It works a treat!