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

inolab

New Member
Joined
Sep 28, 2016
Messages
16
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.


HTML:
[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]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

inolab

New Member
Joined
Sep 28, 2016
Messages
16
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)

Code:
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

New Member
Joined
Sep 28, 2016
Messages
16
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:

Code:
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
 

Forum statistics

Threads
1,136,790
Messages
5,677,743
Members
419,718
Latest member
ALWP

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
Top