[Q] How to sort tabs in order of CodeName

[John]

Is there a way in VBA to sort the worksheets/tabs in the order of each
worksheet's CodeName? The code below sorts the sheets in alphabetical
order, but how can change it to sort via CodeName?

Sub SortWorksheets()
' sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
Application.ScreenUpdating = False
sCount = Sheets.Count
If sCount = 1 Then Exit Sub
For i = 1 To sCount - 1
For j = i + 1 To sCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
End Sub

 

[John]

Looks like I found some code that will do this. In case anyone is
interested....

Sub SortSheetsCodeName()
Application.ScreenUpdating = False
Dim iSheets%, i%, j%, ws As Worksheet
iSheets = Sheets.Count
For Each ws In Worksheets
If Len(ws.CodeName) = 6 Then
For i = 1 To iSheets - 1
For j = i + 1 To iSheets
If Val(Right(Sheets(j).CodeName, 1)) <
Val(Right(Sheets(i).CodeName, 1)) _
Then Sheets(j).Move before:=Sheets(i)
Next j
Next i
End If
Next ws

For Each ws In Worksheets
If Len(ws.CodeName) > 6 Then
For i = 1 To iSheets - 1
For j = i + 1 To iSheets
If Val(Right(Sheets(j).CodeName, 2)) < Val(Right(Sheets(i).CodeName,
2)) _
Then Sheets(j).Move before:=Sheets(i)
Next j
Next i
End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[EGMono]

Why can't you simply use the "<",">" operators with the codenames?
(Keep in my I haven't tested it... but I'm thinking a recursive
function would simplify the code


Option Explicit
Option Compare Text

Function SortByCodename(WS as Worksheets) As Worksheets
Dim Done As Boolean
Done = True
For i = 1 To WS.Count - 1
If WS(i).Codename > WS(i+1).Codename Then
WS(i+1).Move Before:= WS(i)
Done = False
End If
Next
If Done Then
Set SortByCodename = WS
Else
Set SortByCodename = SortByCodename( WS )
End If
End Function

Sub TestSortByCodename()
With Application
.ScreenUpdating = False
With ThisWorkbook
Set .Worksheets = SortByCodename( .Worksheets )
End With
.ScreenUpdating = True
End With
End Sub