Excel VBA: Sort sheets that only have a comma in the tab name

[jcbarte]

Hi there
I'm fairly new to VBA so please be patient with me I have a workbook that will have 60+ sheets. These will be named with clients names, then I have my working sheets. I am trying to sort my workbook by sorting alphabetically those tabs with a "," only but I can't get the script to work

Here is what I have:
Sub SortSheets()




Dim ws As Worksheet
Dim ShCount As Integer, i As Integer, j As Integer
Application.DisplayAlerts = False


For Each ws In Worksheets
ShCount = Sheets.Count
Next
For i = 1 To ShCount - 1
For j = i + 1 To ShCount

if( Sheets(j).ws.Name(",") > ( Sheets(i)ws.Name(",")) Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i




Application.DisplayAlerts = True
End Sub


Any help would be very much appreciated.
Thank you

 

[shknbk2]

Welcome to using VBA. You'll get better the more you practice.

Your code is a good effort, but as you already know, it doesn't work. There are a few reasons why the code doesn't work as expected, but it would take going through it patiently and likely with more flexibility than there is here. I've drafted something I think will work not having seen your spreadsheet, but it works on a temp workbook I created and based on what I understand is your need.

Let me know how it works.

Sub SortSheets()
    Dim sheetsArray() As String    Dim ws As Worksheet
    Dim i As Integer
    Dim stringTemp As String
    
    'Get sheets with commas into an array
    i = -1
    For Each ws In Worksheets
        If InStr(1, ws.Name, ",") > 0 Then
            i = i + 1
            ReDim Preserve sheetsArray(i)
            sheetsArray(i) = ws.Name
        End If
    Next ws
    
    If i > -1 Then  'make sure something was added to sheetsArray
        'Sort array alphabetically
        Dim sorted As Boolean
        sorted = False
        Do While Not sorted
            sorted = True
            For i = 0 To UBound(sheetsArray) - 1
                If sheetsArray(i) > sheetsArray(i + 1) Then
                    stringTemp = sheetsArray(i)
                    sheetsArray(i) = sheetsArray(i + 1)
                    sheetsArray(i + 1) = stringTemp
                    sorted = False
                    Exit For
                End If
            Next i
        Loop
        
        'Move sheets
        Application.DisplayAlerts = False
        For i = UBound(sheetsArray) - 1 To 0 Step -1
            Sheets(sheetsArray(i)).Move Before:=Sheets(sheetsArray(i + 1))
        Next i
        Application.DisplayAlerts = True
    End If
End Sub

 

[Fluff]

Another option

Sub jcbarte()
   Dim Lst As Object
   Dim Ws As Worksheet
   Dim i As Long
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Ws In Worksheets
       If InStr(1, Ws.Name, ",") > 0 Then Lst.add Ws.Name
   Next Ws
   Lst.Sort
   For i = 0 To Lst.Count - 1
      Sheets(Lst(i)).Move Sheets(i + 1)
   Next i
End Sub

 

[shknbk2]

Ha! Wonderful! Much shorter.

However, I think you should move the sheets going backward through the list. For example, if going forward and the sheets are ordered A, C, B, then I think you would end up with B, C, A (first move: C, A, B; second move: B, C, A). Thus, I would change the line to this

[COLOR=#333333]For i = Lst.Count - 1 to 0 Step -1[/COLOR]

 

[jcbarte]

Thank you both so much!!! That worked

 

[Fluff]

However, I think you should move the sheets going backward through the list. For example, if going forward and the sheets are ordered A, C, B, then I think you would end up with B, C, A

Nope, you would end up with A, C, B.
It moves the first sheet in the list to position 1, the 2nd to position 2 etc.

 

[Fluff]

Thank you both so much!!! That worked

Glad we could help & thanks for the feedback