In some applications, it may be useful to have the worksheets in alphabetical order.  For example, if you have a worksheet for each employee on a team and each employee has their own worksheet, you may want these sheets in alphabetical order. You could do this manually, but if you have more than a few sheets, it would be easier to automate the task.   Excel does not have a built in tool to do this, but you can use some fairly simple VBA code accomplish this. 

The following code will sort the sheets in the workbook, in alphabetical order. 

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
    FirstWSToSort = 1
    LastWSToSort = Worksheets.Count
Else
    With ActiveWindow.SelectedSheets
        For N = 2 To .Count
            If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                MsgBox "You cannot sort non-adjacent sheets"
                Exit Sub
            End If
        Next N
        FirstWSToSort = .Item(1).Index
        LastWSToSort = .Item(.Count).Index
     End With
End If

For M = FirstWSToSort To LastWSToSort
    For N = M To LastWSToSort
        If SortDescending = True Then
            If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                Worksheets(N).Move Before:=Worksheets(M)
            End If
        Else
            If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
               Worksheets(N).Move Before:=Worksheets(M)
            End If
        End If
     Next N
Next M

End Sub

As written above, the code will sort all of the worksheets in ascending order.  To sort in descending order, you can change SortDescending  to  True.   You may not want to sort all of the sheets.  For example, if you have a summary sheet at either the beginning or end of the workbook, you may not want to include this in the sort.  To start the sort after the one or more sheets, change the value of FirstWSToSort to the index number of the first worksheet to sort.  For example, to leave the first two worksheets in place, change the value to 3.  Similarly, to leave the last two sheets in place, change the value of LastWSToSort to  Worksheets.Count - 2. 

If you don't know what the worksheet index number is, or you want to use the worksheet name instead of the index number, you can retrieve the sheet's index number with the Index property.  For example, 

FirstWSToSort = Worksheets("SomeSheet").Index

You can also select the sheets to sort by clicking on the tab of the first sheet to sort, holding down the SHIFT key, and clicking the tab of the last sheet to sort.  The code will then sort only those sheets.  The sheets to sort must be adjacent.  You cannot sort non-adjacent sheets.
 

Sorting Worksheets In Numerical Order
The code above sorts in alphabetic order, which means that, for example, Sheet10 would follow Sheet1 rather than Sheet9. If you want to sort by true numeric order, and all your sheets are named SheetN (where N is a number), use the following code:

Sub SortWorksheetsNumeric()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
    FirstWSToSort = 1
    LastWSToSort = Worksheets.Count
Else
    With ActiveWindow.SelectedSheets
        For N = 2 To .Count
            If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                MsgBox "You cannot sort non-adjacent sheets"
                Exit Sub
            End If
        Next N
        FirstWSToSort = .Item(1).Index
        LastWSToSort = .Item(.Count).Index
    End With
End If

For M = FirstWSToSort To LastWSToSort
    For N = M To LastWSToSort
         If SortDescending = True Then
            If CInt(Mid(Worksheets(N).Name, 6)) > _
                   CInt(Mid(Worksheets(M).Name, 6)) Then
                Worksheets(N).Move Before:=Worksheets(M)
            End If
        Else
            If CInt(Mid(Worksheets(N).Name, 6)) < _
                  CInt(Mid(Worksheets(M).Name, 6)) Then
                Worksheets(N).Move Before:=Worksheets(M)
            End If
        End If
    Next N
Next M

End Sub



 

Sorting In Custom Order

You can also sort worksheets in a custom order.  For example, if you have the name of your worksheets in cells A1:A3 on a sheet named CSheet, in the order you want them sorted, use code like the following to move the sheets in to the proper order:

Sub SortWS2()

Dim SortOrder As Variant
Dim Ndx As Long
With Worksheets("CSheet").Range("A1:A3")
    For Ndx = .Cells.Count To 1 Step -1
        Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
    Next Ndx
End With
End Sub
 

You can use similar code if you have worksheet names in an array, in the order you want the arranged.  The code below will arrange the sheets in the order in which their names appear in the array variable SortOrder.

Sub SortWS3()

Dim SortOrder As Variant
Dim Ndx As Long
SortOrder = Array("CSheet", "ASheet", "BSheet")
For Ndx = UBound(SortOrder) To LBound(SortOrder) Step -1
    Worksheets(SortOrder(Ndx)).Move before:=Worksheets(1)
Next Ndx

End Sub
 

Grouping Sheets By Color

Excel 2002 and later versions allow you to color the worksheet tabs. The code below will group the sheets according to color.  Within each color group, the original order is preserved (e.g., blue sheets will be grouped together, but with the group of blue sheets, the order will be the same as before the sheets were grouped).  This will work only in Excel 2002 and later.  Earlier version do not allow colored tabs, so the code won't work.

Sub GroupSheetsByColor()
Dim Ndx As Long
Dim Ndx2 As Long
For Ndx = 1 To Worksheets.Count - 1
    For Ndx2 = Ndx To Worksheets.Count
        If Worksheets(Ndx2).Tab.ColorIndex = _
           Worksheets(Ndx).Tab.ColorIndex Then
            Worksheets(Ndx2).Move after:=Worksheets(Ndx)
        End If
    Next Ndx2
Next Ndx
End Sub