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
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.
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)
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.
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.
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:
- 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
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.
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.
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:
- 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.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: