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.

Contents

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 pages which may be useful to you are:

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: