Excel VBA auto-sort by date

did none of the 541000 findings on google for vba excel sort catch your interest? Check if this leads you in the right direction please but adjust the range for the data you want to sort, check about the headers etc:

Sub EGS_CVS_Sorting()
Dim lr As Long, lr2 As Long, r As Long

    lr = Sheets("template").Cells(Rows.Count, "L").End(xlUp).Row

    For r = lr To 2 Step -1

        Select Case Sheets("template").Range("L" & r).Value
            Case Is = "1a"
                lr2 = Sheets("EGS lines").Cells(Rows.Count, "L").End(xlUp).Row
                Sheets("template").Rows(r).Copy Destination:=Sheets("EGS lines").Range("A" & lr2 + 1)

            Case Is = "1b"
                lr2 = Sheets("CVS lines").Cells(Rows.Count, "L").End(xlUp).Row
                Sheets("template").Rows(r).Copy Destination:=Sheets("CVS lines").Range("A" & lr2 + 1)
        End Select

    Next r
    With Sheets("EGS lines")
        lr = .Cells(Rows.Count, "L").End(xlUp).Row
        Range("A1:L" & lastrow).Sort key1:=Range("J1:J" & lr), _
           order1:=xlAscending, Header:=xlYes
    End With
    With Sheets("CVS lines")
        lr = .Cells(Rows.Count, "L").End(xlUp).Row
        Range("A1:L" & lastrow).Sort key1:=Range("J1:J" & lr), _
           order1:=xlAscending, Header:=xlYes
    End With
End Sub

A good starting point for writing a macro to perform some task you haven't coded before is to simply record a macro doing the task you want done. So if I start with a sample data set that has a date in column J, I start recording a macro, sort the data by column J, stop the recording, and look at the code. I get this:

Sub Sorter()
'
' Sort Macro
'

'
    Range("J1").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:= _
        Range("J1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("All Active Clients").Sort
        .SetRange Range("F2:J23")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This manually codes that I've selected cell J1, then shows me the steps that the sort function goes through. From this, I can narrow down what I actually want to do. For example, selecting J1 is unnecessary, I don't need to worry about the .sortmethod, etc. I can trim the code down to something like the following:

Sub Sorter()
'
' Sort Macro
'

'
    Const csDateSt As String = "J1"

    Dim shtSort As Worksheet
    Dim rngSort As Range

    Set shtSort = Sheets("Sheet1")
    Set rngSort = shtSort.UsedRange

    With shtSort.Sort
        .SortFields.Clear
        .SortFields.Add Key:= _
                        Range(csDateSt), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending
        .SetRange rngSort
        .Header = xlNo
        .Apply
    End With
End Sub

All I have done is reorganise the recorded code, changed a few hard-coded values to constants and variables, and put it all in a With block. I can now use this as a roadmap to put the same kind of structure wherever I need it.

You could even keep your Sorting process as a separate Sub, and just call it when needed, passing it arguments to tell it where the data was, thus:

Sub Sorter(ByVal shtSort As Worksheet, ByVal rngSort As Range, ByVal strKey As String)
    With shtSort.Sort
        .SortFields.Clear
        .SortFields.Add Key:= _
                        Range(strKey), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending
        .SetRange rngSort
        .Header = xlNo
        .Apply
    End With
End Sub

Then in your loop you would say something along the lines of:

    Select Case Sheets("template").Range("L" & r).Value
        Case Is = "1a"
            lr2 = Sheets("EGS lines").Cells(Rows.Count, "L").End(xlUp).Row
            Sheets("template").Rows(r).Copy Destination:=Sheets("EGS lines").Range("A" & lr2 + 1)

        Case Is = "1b"
            lr2 = Sheets("CVS lines").Cells(Rows.Count, "L").End(xlUp).Row
            Sheets("template").Rows(r).Copy Destination:=Sheets("CVS lines").Range("A" & lr2 + 1)
    End Select

Call Sorter(Sheets("EGS Lines"),Sheets("EGS Lines").range("A1").currentregion, "J1")
Call Sorter(Sheets("CVS Lines"),Sheets("CVS Lines").range("A1").currentregion, "J1")