VBA Help Auto-Sort upon workbook Open

[Rob_010101]

Hello

This is my sheet (unfortunately can't install XL2BB on my work machine):

Sheet is called "outstanding" and is 16 columns wide. I already have VB code in here which moves the row to another sheet based on the status from a drop-down list in column O.

When the workbook is opened (or closed, whichever), I would like the "outstanding" sheet to be sorted by:

Column J > date > oldest to newest

Kind Regards
Chris

 

[benlinford]

You can add this code in a new or existing code module;

Sub SortOutstanding()

    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = ThisWorkbook.Worksheets("outstanding")
    Set rng = ws.Range("A1").CurrentRegion
    
    With ws
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=rng.Cells(1, 10), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        With ws.Sort
            .SetRange rng
            .Header = xlYes
            .Apply
        End With
    End With

End Sub

Then you can add this code in your ThisWorkbook code module to call the above sub when the Workbook Open event fires;

Private Sub Workbook_Open()

    Call SortOutstanding

End Sub

 

[Rob_010101]

Thank you very much