Text Functions‎ > ‎

### Sorting Sheets

PROBLEM:
"I have a workbook with over 100 sheets, they are all randomly ordered.  Can we sort the sheets so they are in alphabetical order?"

#### CODE

Option Explicit

Sub SortSheetsAlphabetically()
'Author:    Jerry Beaucaire,  8/16/2012
'Summary    Sorts worksheets alphabetically
Dim Arr As Variant, vTemp As Variant, ws As Worksheet
Dim i As Long, j As Long, buf As String

'Collect the sheet names into an array
For Each ws In Worksheets
buf = buf & "," & ws.Name
Next ws
Arr = Split(Mid(buf, 2, Len(buf)), ",")

'from John Walkenbach’s Excel Power Programming with VBA to sort the array
For i = LBound(Arr, 1) To UBound(Arr, 1) - 1
For j = i + 1 To UBound(Arr, 1)
If Arr(i) > Arr(j) Then
vTemp = Arr(i)
Arr(i) = Arr(j)
Arr(j) = vTemp
End If
Next j
Next i

'Sort the worksheets
For i = LBound(Arr, 1) To UBound(Arr, 1)
Sheets(Arr(i)).Move after:=Sheets(Sheets.Count)
Next i

End Sub
The CreateHyperlinkedSheets is highly recommended for dealing with workbooks with a large number of sheets for quick navigation.

Nothing says "thanks" like a steak dinner!