Copy worksheet with current date as part of variable name

NoSparks · #6 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Setting a Worksheet Name as a Variable Help	rod147	Excel Programming	3	11-25-2016 08:50 AM
Date picker content control to always show current date.	lucky16	Word VBA	2	07-01-2016 01:14 PM
Variable to rename a tab in a worksheet via VBA	Chayes	Excel Programming	5	08-07-2012 02:03 AM
Meaning of current date vs status date when saving baselines	ketanco	Project	1	02-08-2012 02:20 PM
How to call current PC date and/or current PC year	KIM SOLIS	Excel	2	11-04-2011 06:09 PM