VBA- How to sort worksheets based on a number on their name?-CodePudding

I am new and I am working in a VBA - Excel Project. My project has some Sub-projects, lets call them A,B,C... Through VBA code and some actions I create new sheets that are called A-item-1, A-item-2 or B-item-1 or with C.

The thing is that I am creating these sheets by copying a template and printing it before the next letter. For example, B-item-3 is created copying my B-item-template and printing it before C sheet.

Now, the way the program works you can create B-item-1, B-item-4, hit the button and it will create them. If after that you want to create B-item-2, as the program puts it before C you will have this:

A,B,B-item-1,B-item-4,B-item-2,C (this is my workbook)

And I am thinking on a code to rearrange only "B-item-X" sheets.

As far as i know i should put these sheets in an Array. Then i should, somehow, get the number from every sheet to a variable. And then compare with a for these sheets and if the number is less than the sheet i am comparing it to, move that sheet.

I think it could look like this?

1st - Determine the Range of sheets I want to rearrange
2nd - Somehow extract the number of each "B-item-X" sheet
3rd-->

    For i=1 to Range of sheets -1
          For j= i   1 to Range of sheets
             if The number on the name of the (j) Sheet is < The number on the name of the (i) Sheet then
               Sheets(j).Move before:= Sheets(i)
             End if
          Next j
       Next i

I hope it is easy to understand what i want to do. If not, hit me up and i will try to explaint it with more details. I hope someone can help me. Thank you very much.

Edit: The way the program works. The user writes an input and based on that I have a non visible chart in which i write 0 or 1 depending on the input of the user. Then the program creates the ITEM sheet if it sees a 1 on the chart. As an example, for the 20 items i can create: The user puts YES (1 in the Chart) on ITEMS 1,7 and 15 and presses the button OK. The program creates them and you would have these sheets: A,B,B-item-1,B-item-7,B-item-15,C,D... But the program is still used for next Batches, lets say. So the next day the user puts YES on the item 9. The program will create the "B-item-9" sheet before C sheet but it will be put after the "B-item-15" sheet because it was already crated the day before that. The thing is i do not know how to move them to the right place when creating them nor do i know how to rearrange them...

CodePudding user response：

Try this

Sub SortSheetsTabName()
    Dim scrUpdating As Boolean: scrUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    Const txtBefNum As String = "B-item-"

    With ThisWorkbook
        For i = 1 To .Sheets.Count - 1
            If .Sheets(i).Name Like txtBefNum & "*" And _
               IsNumeric(Mid(.Sheets(i).Name, Len(txtBefNum)   1)) Then
                For j = i   1 To .Sheets.Count
                    If .Sheets(j).Name Like txtBefNum & "*" And _
                       IsNumeric(Mid(.Sheets(j).Name, Len(txtBefNum)   1)) Then
                        If CLng(Mid(.Sheets(i).Name, Len(txtBefNum)   1)) > _
                           CLng(Mid(.Sheets(j).Name, Len(txtBefNum)   1)) Then
                            .Sheets(j).Move before:=.Sheets(i)
                        End If
                    End If
                Next j
            End If
        Next i
    End With
    Application.ScreenUpdating = scrUpdating
End Sub

This will sort all worksheets that have a name starting with 'B-item-' according to the number in their name following that text from smallest to largest.