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

Question

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...

Are you trying to solve the problem to *add* a sheet at the right place or to *sort* the sheets of an existing workbook where the sheets are already in the wrong order? And it the template a sheet within the workbook or do you copy the template sheet from a template workbook? — FunThomas, Nov 28 '22 at 11:33
Well it would be great if i had the knowledge to add the sheet to the right place but the program copies the template from the workbook (i make it invisible so the user can not see it after creating them), then pastes it before the next letter and after that goes to the next item. I have maximum 20 items and the program search through them and creates the ones that have to be created, so i really dont know where i have to put them until the user clicks on the items he wants to create... — XABIER, Nov 28 '22 at 11:37
Welcome to SO. Not tested but you could take all sheets names into an array, [short the array](https://stackoverflow.com/a/152325/9199828) and then with a loop keep moving all sheets until their indexes fit the indexes of your sorted array. — Foxfire And Burns And Burns, Nov 28 '22 at 11:40
Simple Internet Search reveals this article from Microsoft: [Sort Worksheets Alphanumerically by Name](https://learn.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/sort-worksheets-alphanumerically-by-name). Very similar to what you show. Did you try it? — Ron Rosenfeld, Nov 28 '22 at 12:10
Just don't forget to use Left(Sheets(i).Name,1) = B or C depending on the situation when you use those sorts so your A/C sheets remain untouched — Notus_Panda, Nov 28 '22 at 12:35
Good morning, i have come to the idea of splitting the names and saving their number (sheet named item-1 splitted between "-" so i get the 1). Then i will change the Sheets name to their number so i can reorganize them alphanumerically and finally i will change again their name just adding "Item-" so they're the same name as before. The problem is i do not know how to establish the range of only B-item-X sheets instead of "For i=1 to sheets.count"... Any help? — XABIER, Nov 29 '22 at 07:31

GWD · Accepted Answer · 2022-11-30T08:13:55.067

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.

It works beautifully! I modded it a little bit to work with all my program but this code is perfect! Thank you very much! — XABIER, Nov 30 '22 at 07:07
Hi @XABIER, just FYI and in case you decide to stay around on this site, you might want to take a look at [this](https://stackoverflow.com/help/someone-answers#:~:text=To%20accept%20an%20answer%3A,the%20answer%2C%20at%20any%20time.). Perhaps surprisingly, comments saying just thank you are actually discouraged ‍♂️ — GWD, Nov 30 '22 at 08:26

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

1 Answers1