VBA code to sort a variably selected area in current workbook with variable sheet names

[inolab]

I keep hoping one day I will be able to write code and not ask for help - but alas I need HELP still.

Background:

we work in 140 different workbooks and we change their worksheet name every week.
each workbook has a variable number of rows that we will select and then sort
the rows we are sorting do not start at 1 or have a set range each week

We have to take selected data in our worksheet and sort it by f, a, b, c and e.
I would like to write a macro that we can run that will sort the selected area for us.

[CODE]Sub sort_completed_worksheet()
'
' sort_completed_worksheet Macro
' this macro is used to sort our invoices after completion by: eft date(f), last name(a), first name(b), pt acct#(c), dos(d)
'
'
Dim wksData As Worksheet
    Dim rngData As Range
    Set wksData = ActiveSheet
    Set rngData = Selection
    
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Clear    THIS SHEET NAME WILL CHANGE EACH WEEK AND WITH EACH WORKBOOK
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "F5467:F5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal                                                                   THE SELECTED RANGE WILL CHANGE EACH WEEK AND WITH EACH WORKBOOK
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "A5467:A5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "B5467:B5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "C5467:C5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "E5467:E5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("56233").Sort
        .SetRange Rows("5467:5578")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

[/CODE]

 

[inolab]

sorry - I listed what I was trying to fix and it probably doesn't make sense.

here is my macro recorder sort ( which works for these specific rows, but I want to be able to select the rows I want sorted and then run the macro to sort instead of choosing all 5 columns to sort by)

Sub TEST_SORTING_EOB_DETAILS()
'
' TEST_SORTING_EOB_DETAILS Macro
'
'
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "F5467:F5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "A5467:A5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "B5467:B5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "C5467:C5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("56233").Sort.SortFields.Add Key:=Range( _
        "E5467:E5578"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("56233").Sort
        .SetRange Rows("5467:5578")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[inolab]

Never mind everyone. I found a fix for it from p2p.wrox and in case anyone else wants to know the solution for sorting within a user specified, selected area - this code works:

Sub TEST_SORTING_EOB_DETAILS()
'
' TEST_SORTING_EOB_DETAILS Macro
'
'use the keyword "Selection" for the currently selected range
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Selection.Columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .SortFields.Add Key:=Selection.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .SortFields.Add Key:=Selection.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .SortFields.Add Key:=Selection.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .SortFields.Add Key:=Selection.Columns(5), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .SetRange Selection
    .Apply
    End With
End Sub