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
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
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy

Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.