ID: 9

Category: Excel Automation

Question: How do I alphabetically sort the sheets in the workbook?

Answer: Put the following in a standard module of a workbook. Use it to sort the active workbook

Option Explicit

Sub sortSheets()
    'Sorts the sheets in the activeworkbook by sheet name.  Uses a new _
     XL workbook to actually sort the name.  This allows for a single _
     pass algorithm, such that each sheet is moved into the correct _
     position the one and only time it is moved. _
     For alternative algorithms see _
     XL: How to Sort Sheets in a Workbook _
     http://support.microsoft.com/default.aspx?scid=kb;en-us;812386 _
     and _
     Sorting Worksheets In A Workbook _
     http://www.cpearson.com/excel/sortws.htm
    Const sortAscending As Boolean = True
    Dim srcWB As Workbook, tempWB As Workbook, aWKS As Worksheet, _
        i As Long
    Set srcWB = ActiveWorkbook
    If srcWB.Sheets.Count = 1 Then Exit Sub     '<<<<
    Set tempWB = Workbooks.Add
    Set aWKS = tempWB.Worksheets.Add
    aWKS.UsedRange.ClearContents
    With aWKS.Range("a1")
    For i = 1 To srcWB.Sheets.Count
        .Offset(i - 1, 0).Value = srcWB.Sheets(i).Name
        Next i
        End With
    aWKS.Range("A1").End(xlDown).Sort Key1:=Range("A1"), _
        Order1:=IIf(sortAscending, xlAscending, xlDescending), _
        Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    With srcWB
    For i = srcWB.Sheets.Count To 1 Step -1
        .Sheets(aWKS.Range("a1").Offset(i - 1, 0).Value).Move _
            before:=.Sheets(1)
        Next i
        End With
    tempWB.Close False
    End Sub

ExtendedLink: