how to rearrange excel sheet in alphabetical order based on sheetname,but i have one specific rule if sheet name contain "YTD" then its always 1st

Help me to rearrange excel sheet in alphabetical order based on sheetname, but i have one specific rule if sheet name contain "YTD" then its always 1st and if we have number 0 then it will always be at the end of its group

thank you

Input :

• YTD-AA-
• 1_AB
• 3_AB
• 2_AB
• 0_AB
• 2_NC
• 0_NC
• 1_NC

and i would like to arrange it like :

• YTD-AA (excel sheet with “YTD” in the word will alwyas be the first )
• 1_AB (follow up by the LEAST number and FIRST number found in order of A- Z)
• 2_AB (follow up by the same group as the previous one (AB)
• 3_AB
• 0_AB (Ended in number ZERO ‘0’ on the same group (AB)
• 1_NC (Continue on the next group after A - B - C etc. but always start again in the LEAST NUMBER MEANS IT CAN START FROM 2 IF 1 is not present )
• 2_NC (follow up in the same group (NC) but now on next number after the previous
• 0_NC (ended with number 0 )

You can divide your work into 2 tasks: a) How to sort at all b) How to tell the exact order.

Any library sorting routine implements a more or less sophisticated sorting algorithm (Bubble Sort, Quick Sort, Heap Sort and the like). Inside the sorting, two objects are compared to answer the question which of those 2 objects "comes first". For strings, you usually use alphabetical order (that rises already several question like case sensibility, non-7bit Ascii characters and so on), for numbers, you compare the values. When you want to sort files, you can have different criterias: Name, Size, Date...

Now the point is no matter how you compare the objects, the sorting algorithm itself remains the same. Therefore, the most common implementation is that the sorting routine calls a function that answers the question which of two objects is smaller (= comes first). Usually, that function returns a negative number if the first object is smaller, a positive if the second object is smaller and 0 if both are the same.

As you have only few sheets in a workbook (and yes, even 100 is a small number when it comes to sorting), a simple Bubble sort will do. In the question that is linked in the comments (Excel VBA: Sort Sheets in Alphanumeric Order), the following code is used (slightly modified by me)

Sub SortWorksheets()
    Dim i As Long, j As Long
    For i = 1 To Worksheets.Count - 1
        For j = i + 1 To Worksheets.Count 
            If Worksheets(j).Name < Worksheets(i).Name Then
                Worksheets(j).Move before:=Worksheets(i)
            End If    
        Next j
    Next i
End Sub

Now that routine answers the question which sheet is "smaller" by itself: it compares the sheet names directly. Now lets move this comparison to a function:

            If compare(Worksheets(j).Name, Worksheets(i).Name) < 0 Then
                Worksheets(j).Move before:=Worksheets(i)
            End If    

And have a function

Function compare(name1 As String, name2 As String) as Long
    compare = Iif(name1 < name2, -1, 1)
End Function

Exact the same functionality, but the comparison is "outsorced". And if you now want to have a different order logic, all you have to do is to change that compare-function. In your case, that could be something like this:

Function compare(name1 As String, name2 As String) As Long

    ' (1) Handle the YTD-Case:
    If Left(name1, 3) = "YTD" And Left(name2, 3) <> "YTD" Then
        compare = -1
    ElseIf Left(name1, 3) <> "YTD" And Left(name2, 3) = "YTD" Then
        compare = 1
    Else
        ' Compare the names without the leading number
        Dim pieces1() As String, pieces2() As String
        pieces1 = Split(name1, "_")
        pieces2 = Split(name2, "_")
    
        If UBound(pieces1) > 0 And UBound(pieces2) > 0 Then
            If pieces1(1) <> pieces2(1) Then
                ' different groups
                compare = IIf(pieces1(1) < pieces2(1), -1, 1)
            Else
                ' Same group, compare numbers
                If pieces1(0) = "0" Then
                    compare = 1
                    Exit Function
                ElseIf pieces2(0) = "0" Then
                    compare = -1
                    Exit Function
                Else
                    compare = Val(pieces1(0)) - Val(pieces2(0))
                End If
            End If
        Else
            ' Whatever that is: Compare names directly
            compare = IIf(name1 < name2, -1, 1)
        End If
    End If
End Function