VBA to sort sheets alphabetically

When working with Excel, the worksheets order matters.  The tabs should reflect the flow which a user can understand.  Since there is no native feature to do it, using VBA to short sheets alphabetically can be a useful time-saver.

But things are never as straightforward, are they?

• Sorting can be in standard or reverse order
• In Excel, sheets and worksheets are different.  Worksheets are only those with cells, while sheets can include worksheet and charts sheets
• Worksheet names containing numbers are not sorted as numbers but as text

In this post, I will give you the necessary code to deal with all these situations.

Table of Contents

• VBA to sort worksheets alphabetically
• Adapting the code
• Reverse the order
• Change the workbook
• Sort numbers as numbers
• Sort sheets instead of worksheets

VBA to sort worksheets alphabetically

Enter the following code into a standard code module:

Sub SortWorksheetsAlphabetially()

'Turn off screen updating
Application.ScreenUpdating = False

'Create variables
Dim wb As Workbook
Dim wsCount As Integer
Dim i As Integer
Dim j As Integer

'Declare the workbook
Set wb = Workbooks(ActiveWorkbook.Name)

'Count the number of worksheets
wsCount = wb.Worksheets.Count

'Loop through all worksheets and move
For i = 1 To wsCount - 1
    For j = i + 1 To wsCount
        If wb.Worksheets(j).Name < wb.Worksheets(i).Name Then
            wb.Worksheets(j).Move before:=wb.Worksheets(i)
        End If
    Next j
Next i

'Turn on screen updating
Application.ScreenUpdating = True

End Sub

Adapting the code

The specific example above will put the worksheets in the active workbook into alphabetical order.  But we can customize this in many ways:

• Reverse the order
• Apply to other workbooks
• Sort numbers as numbers
• Sort including chart sheets

Let’s take a look at these options in turn.

Reverse the order

To reverse the order, we change the direction logic sign.

Change this line of code:

If wb.Worksheets(j).Name < wb.Worksheets(i).Name Then

to this:

If wb.Worksheets(j).Name > wb.Worksheets(i).Name Then

Change the workbook

The VBA code can also be changed to work on any open workbooks.

Active workbook:

This is the code currently used in the example. If you want to change the code, switch this line to one of the lines below.

Set wb = Workbooks(ActiveWorkbook.Name)

Named workbook:

Set wb = Workbooks("WorkbookName.xlsx")

Remember to change WorkbookName.xlsx for the name of your workbook.

Nth workbook opened

Set wb = Workbooks(1)

The number refers to workbooks in the order opened, where 1 is the first workbook, 2 is the second workbook, etc.

Sort numbers as numbers

All worksheet names are text strings.  Therefore, if we had sheets named “1”, “2”, and “12”, their sorted order would be “1”, “12” and “2”.  But we can change the basic code to sort numbers as numbers.

All changes from the original code have been highlighted in bold.

Sub SortWorksheetsAlphabetiallyAndNumerically()

'Turn off screen updating
Application.ScreenUpdating = False

'Create variables
Dim wb As Workbook
Dim wsCount As Integer
Dim i As Integer
Dim j As Integer
Dim wsName1 As Variant
Dim wsName2 As Variant

'Declare the workbook
Set wb = Workbooks(ActiveWorkbook.Name)

'Count the number of worksheets
wsCount = wb.Worksheets.Count

'Loop through all worksheets and move
For i = 1 To wsCount - 1
    For j = i + 1 To wsCount

        'Capture worksheet names
        wsName1 = wb.Worksheets(i).Name
        wsName2 = wb.Worksheets(j).Name

        'Convert numbers
        If IsNumeric(wsName1) = True Then wsName1 = CLng(wsName1)
        If IsNumeric(wsName2) = True Then wsName2 = CLng(wsName2)

        'Sort worksheets
        If wsName2 < wsName1 Then
            wb.Worksheets(j).Move before:=wb.Worksheets(i)
        End If

    Next j
Next i

'Turn on screen updating
Application.ScreenUpdating = True

End Sub

Sort sheets instead of worksheets

As stated above, chart sheets are included in the sheets collection, but not the worksheets collection.  Therefore to include chart sheets into the scope of the code, we can use the following code as a base.

The changes from the original code have been highlighted in bold.

Sub SortSheetsAlphabetially()

'Turn off screen updating
Application.ScreenUpdating = False

'Create variables
Dim wb As Workbook
Dim shtCount As Integer
Dim i As Integer
Dim j As Integer

'Declare the workbook
Set wb = Workbooks(ActiveWorkbook.Name)

'Count the number of worksheets
shtCount = wb.Sheets.Count

'Loop through all worksheets and move
For i = 1 To shtCount - 1
    For j = i + 1 To shtCount
        If wb.Sheets(j).Name < wb.Sheets(i).Name Then
            wb.Sheets(j).Move before:=wb.Sheets(i)
        End If
    Next j
Next i

'Turn on screen updating
Application.ScreenUpdating = True

End Sub

The same amendments to reverse sort, sort numbers, and sort other workbooks can also be made to this code.

Related posts:

• Hide all sheets except one with Office Scripts
• Loop through selected sheets with VBA
• Common VBA properties and methods for worksheets
• 30 ready to use VBA macros

---

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy