Sort Excel worksheets based on name, which is a date

Question

So I've got this Excel workbook that has some macro's. Users are presented with a button to either create a worksheet with the current date as name, or enter a date manually and that worksheet will be created.

Now the issue: The worksheet has two sheet ('Initial' and 'Version') that must be first and last. However, all worksheets created in between should be sorted on date everytime a new sheet is created. And I mean sorted on date, the sheets are 'DD-MM-YY' so e.g. I could have names like '1-11-21', '2-11-21', '11-11-21' and '21-11-21' in the same workbook and it should be sorted ascending.

Any suggestions? A normal sort just messes things up I found (1-11-21 and 11-11-21, followed by '2-11-21' and '21-11-21'....

Thanks,

Jasper

Answer 1

Sorting sheets of a workbook is rather easy, there a numerous examples out there, looking more or less like this:

Sub SortSheets(Optional wb As Workbook = Nothing)
    If wb Is Nothing Then Set wb = ActiveWorkbook  ' (or maybe ThisWorkbook)
    
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    
    For i = 1 To wb.Worksheets.Count - 1
        For j = i + 1 To wb.Worksheets.Count
            ' ==> The following line needs to be replaced!
            If wb.Worksheets(j).Name < wb.Worksheets(i).Name Then
                wb.Worksheets(j).Move before:=wb.Worksheets(i)
            End If
        Next j
    Next i
    ' Application.ScreenUpdating = True
End Sub

The only logic you need to change now is the If-statement. Instead of comparing the names of the sheets, you need to find a custom logic that compares the names of the two sheets.

Your logic is basically: If the name is Initial, sort it to the top, if it is Version, sort it to the end and for all the others, sort them by the date the name is representing.

I created a small function that calculates a number from the name. The Initial sheets gets 0, the Version gets a arbitrary high number, a worksheet with a date in the name gets the date value (a date is basically a double value in VBA) by converting the name into the date. If the name cannot be converted to a date, the value will be so that the sheet will be sorted to the end (but before the version sheet).

Function getSortNumber(ws As Worksheet) As Double
    Const MaxNumber = 100000
    
    If ws.Name = "Initial" Then
        ' Sort Initial to the beginning
        getSortNumber = 0
    ElseIf ws.Name = "Version" Then
        ' Sort Version to the end
        getSortNumber = MaxNumber + ws.Parent.Sheets.Count
    Else
        ' Create real date fom name
        Dim d As Date, tokens() As String
        tokens = Split(ws.Name, "-")
        On Error Resume Next
        d = DateSerial(Val(tokens(2)), Val(tokens(1)), Val(tokens(0)))
        On Error GoTo 0
        If d = 0 Then
            ' Failed to convert to date, sort to end
            getSortNumber = MaxNumber + ws.Index
        Else
            ' Sort according to the date value
            getSortNumber = CDbl(d)
        End If
    End If
End Function

You can adapt the function easily if your needs changed (eg date format, or you can have extra text with the date, or you want to sort the version sheet to the beginning, or you have additional sheets with different names...). The sort function itself will not change at all, only the comparison logic.

Now all you have to do is change the line in the sort routine:

If wb.Worksheets(j).Name < wb.Worksheets(i).Name Then

to

If getSortNumber(wb.Worksheets(j)) < getSortNumber(wb.Worksheets(i)) Then