VBA to sort sheets alphabetically

VBA Code Snippets

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.

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.

100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

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:



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. 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:

Leave a Reply

Your email address will not be published. Required fields are marked *