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

jcbarte

New Member
Joined
Mar 21, 2019
Messages
3
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
253
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.

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,429
Office Version
  1. 365
Platform
  1. Windows
Another option
Code:
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

Active Member
Joined
Mar 5, 2016
Messages
253
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

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

jcbarte

New Member
Joined
Mar 21, 2019
Messages
3

ADVERTISEMENT

Thank you both so much!!! That worked :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,429
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,316
Messages
5,635,521
Members
416,862
Latest member
MGDlite

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top