Ordering worksheet tabs in a specific order

[emu165]

Hi - First time poster. Please let me know if I am doing something incorrectly.
I have a file that will have a varying number of tabs. There will be Summary tabs and Detail tabs. I need to put the summary tab next to the appropriate Detail tab. Summary tabs will always start with Sum_(and then a tabname also will vary). The Detail tabs will start with Detail_(and then the tabname). So, the tabs will come in like this: SUM_1, SUM_X, Sum_Oth, Detail_1, Detail_X, Detail_Oth.
I need it to look like this: Sum_1, Detail_1, Sum_X, Detail_X, Sum_Oth, Detail_Oth.

[Paul_Hossler]

Option Explicit


Sub SortTheTabs()
    Dim i As Long, j As Long, o As Long, n As Long
    Dim arySum() As String, sHold As String, sDetail As String
    Dim ws As Worksheet
    
    ReDim arySum(1 To ThisWorkbook.Worksheets.Count / 2)


    i = 1
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 3) = "Sum" Then
            arySum(i) = ws.Name
            i = i + 1
        End If
    Next
    
    For i = 1 To UBound(arySum) - 1
        For j = i + 1 To UBound(arySum)
            If arySum(i) > arySum(j) Then
                sHold = arySum(i)
                arySum(i) = arySum(j)
                arySum(j) = sHold
            End If
        Next j
    Next i


    o = 1
    For i = 1 To UBound(arySum)
        
        sDetail = "Detail_" & Right(arySum(i), Len(arySum(i)) - 4)
        Worksheets(arySum(i)).Move before:=Worksheets(o)
        Worksheets(sDetail).Move after:=Worksheets(arySum(i))
    
        o = o + 2
    Next i


End Sub

[emu165]

Thanks Paul -- the more I see the more i need to learn. I did get a subscript out of range error on the following line
ReDim arySum(1 To ThisWorkbook.Worksheets.Count / 2)

[Paul_Hossler]

Based on your example, I assumed that there was an even number of worksheet, each "Sum_" matched with a "Detail_", and there were no other sheets

If not true, then I can adjust

[emu165]

There should be an even number. It may not always be what I provided in File1_Before.xlsx, but for every Sum_ there will be a Detail_. I tried to run it using the FILE1_BEFORE.XLSX file.

[Paul_Hossler]

I used your original Before file in #1 and added my macro

[emu165]

Thanks Paul...Not sure what I did but I so appreciate your help!