Durjoy Paul
When we work with lots of data, we need several worksheets to handle them. If you want to arrange them in alphabetical order, you will need to arrange them manually or with the help of the VBA code. In Microsoft Excel, there is no build-in function to arrange worksheets in alphabetical order. This article will mainly focus on how to arrange worksheets in alphabetical order in Excel. I hope you find this article informative and gain much knowledge regarding this problem.
Download Practice Workbook
Download the practice workbook.
2 Easy Methods to Arrange Worksheets in Alphabetical Order in Excel
To arrange worksheets in alphabetical order in Excel, we have found two different methods including the VBA codes. There is no built-in method to arrange worksheets in alphabetical order. Both of the methods are fairly easy to understand. While working with several worksheets, it is really helpful to have an arrangement of those worksheets. To show both of these methods, we take a dataset that includes several worksheets but they are not arranged properly.
1. Arrange Worksheets in Alphabetical Order by Dragging
Our first method is based on arranging manually. As there is no built-in method to arrange worksheets in alphabetical order, you need to do it manually. Follow the steps properly.
Steps
- First, select the tab you want to move.
- Next, drag the tab right or left by clicking the left mouse button.
- There we have our desired result.
- Our fifth worksheet shifts to the first position.
- For all other cases, you can do this and arrange them in alphabetical order.
- As you need to do it manually, it is a very time-consuming process.
2. Embedding VBA Code to Arrange Worksheets in Alphabetical Order
Our next and most effective way is to use the VBA code to arrange worksheets in alphabetical order. Using this code, we can arrange worksheets in ascending order or descending order. In both cases, we have some small changes in code. Follow the steps carefully.
2.1 Arrange Worksheets in Ascending Order
To arrange worksheets in ascending order, we need to use the VBA code which arranges worksheets in alphabetical order from A to Z. Follow the steps carefully to understand the process.
Steps
- First, click on the Developer tab in the ribbon.
- Then, from the Code group, select Visual Basic.
- Some of you don’t have the developer tab in the ribbon. You can add the developer tab by customizing the ribbon or pressing Alt+F11 as the keyboard shortcut.
- It will open up Visual Basic Editor where you can write your VBA Code.
- This will open up the Visual Basic window.
- Then, go to the Insert tab.
- From there, select the Module option.
- It will open up a new code window where you can write down the following code for ascending conditions.
Sub Arrange_Ascending_order()
For a = 1 To Application.Sheets.Count
   For b = 1 To Application.Sheets.Count - 1
       If LCase(Application.Sheets(b).Name) > LCase(Application.Sheets(b + 1).Name) Then
           Sheets(b).Move after:=Sheets(b + 1)
       End If
   Next
Next
End Sub- After that, close the Visual Basic window.
- Then, go to the View tab in the ribbon.
- From the Macros group, select the Macros option.
- A Macro dialog box will pop up.
- From the Macro Name section, select the Arrange_Ascending_order option.
- After that, select Run.
- Now, look at your worksheets all of them are arranged in ascending order.
2.2 Arrange Worksheets in Descending Order
To arrange worksheets in descending order, we need to use the VBA code which arranges worksheets in alphabetical order from Z to A. Follow the steps carefully to understand the process.
Steps
- First, click on the Developer tab in the ribbon.
- Then, from the Code group, select Visual Basic.
- Some of you don’t have the developer tab in the ribbon. You can add the developer tab by customizing the ribbon or pressing Alt+F11 as the keyboard shortcut.
- It will open up Visual Basic Editor where you can write your VBA Code.
- This will open up the Visual Basic window.
- Then, go to the Insert tab.
- From there, select the Module option.
- It will open up a new code window where you can write down the following code for descending conditions.
Sub Arrange_Descending_order()
For a = 1 To Application.Sheets.Count
   For b = 1 To Application.Sheets.Count - 1
       If LCase(Application.Sheets(b).Name) < LCase(Application.Sheets(b + 1).Name) Then
           Sheets(b).Move after:=Sheets(b + 1)
       End If
   Next
Next
End Sub- After that, close the Visual Basic window.
- Then, go to the View tab in the ribbon.
- From the Macros group, select the Macros option.
- Then, a Macro dialog box will pop up.
- From the Macro Name section, select the Arrange_Descending_order option.
- After that, select Run.
- As a result, look at your worksheets all of them are arranged in descending order.
Read More: How to Sort Excel Sheet by Name (4 Easy Methods)
Conclusion
We have shown two different approaches to arranging worksheets in alphabetical order in Excel. These methods include a manual approach and using VBA code. In the VBA code, we have shown how to arrange worksheets in ascending and descending order. So, we try to provide a complete solution regarding the arrangement of worksheets in alphabetical order. If you have questions, feel free to ask in the comment box, and don’t forget to visit our Exceldemy page.
