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!
PayPal - The safer, easier way to pay online!
Comments