Microsoft Office Forums - View Single Post - [Solved] Copy worksheet with current date as part of variable name

NoSparks · 04-22-2018, 08:04 AM

Sure...
Don't know if you want ascending or descending but you can easily alter that.
Change the bottom part of the code where the cursor gets positioned.

Code:

    'position the cursor for sorting
    .Cells(2, 5).Select
    'determine last row
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                             DataOption:=xlSortTextAsNumbers
        .SetRange Range("A2:J" & lr)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
'stop the marching ants
Application.CutCopyMode = False

End Sub

You'll also need to declare lr as long. Do it at the top with the other Dim statements.