Sort Excel worksheets based on name which is a date

Question

I have an Excel workbook with some macros. The current date can be used as the worksheet's name, or the user can manually enter a date and the worksheet will be produced.

The problem is that the worksheet comprises two sheets, Initial and Version, which need to be used in that order. Every time a new worksheet is created, all worksheets made in between should be sorted by date. For example, I may have names like "1-11-21," "2-11-21," "11-11-21," and "21-11-21" in the same worksheet and it should be ordered ascending. The sheets are "DD-MM-YY."

Can someone please help me with this?

Answer 1

Sorting sheets of a workbook are 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

Now, only the If-statement needs to be altered in the logic. You must locate a custom logic that compares the names of the two sheets rather than simply comparing the names of the sheets.

Basically, your reasoning goes like this: If the name is Initial, put it at the top; if it's Version, put it at the bottom; and for all the rest, put them in order of the date the name represents.

I developed a tiny function that derives a value from the name. The Initial sheets receive a value of 0, the Version sheets receive an arbitrarily high number, and a worksheet with a date in the name receives the date value by transforming the name into the date (a date in VBA is essentially a double value). 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). 

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