Sorting excel sheets in numerical order based on the date in the batch number

[Daisy0909]

Hello.

This is something I am struggling with. I have a large number of sheets in an excel spreadsheet that aren’t in date order. They are titled with batch numbers I.e “AB 100921A” or “AB 010616A” etc.

Is there a way to automatically put the sheets in order by the date within the batch number?

I’ve seen many ways to do this if it’s just a date of a word but I’m finding it very difficult to do this with batch numbers.

 

[Akuini]

Hi @Daisy0909. Welcome to the Forum
In your system "100921" means October 9 or September 10?

 

[Daisy0909]

Hi @Daisy0909. Welcome to the Forum
In your system "100921" means October 9 or September 10?

Thankyou for your reply, it is the 10th september

 

[Akuini]

Try it on a copy of your workbook:

Sub SortTabsByDate()

Application.ScreenUpdating = False
Dim ShCount As Long, i As Long, j As Long
Dim n1 As Long, n2 As Long, s1 As String, s2 As String

ShCount = Sheets.Count

For i = 1 To ShCount - 1
    For j = i + 1 To ShCount

        s1 = Sheets(i).Name
        s2 = Sheets(j).Name
        n1 = DateSerial(Mid(s1, 8, 2), Mid(s1, 6, 2), Mid(s1, 4, 2))
        n2 = DateSerial(Mid(s2, 8, 2), Mid(s2, 6, 2), Mid(s2, 4, 2))

        If n2 < n1 Then
            Sheets(j).Move Before:=Sheets(i)
        End If

    Next j
Next i

Application.ScreenUpdating = True

End Sub