If you want to sort Excel sheet by name, this article is for you. Here, we will walk you through 4 easy and effective methods to do the task smoothly.
The following Excel workbook has 8 worksheets. We will use 4 easy methods to sort Excel sheets by name. Here, we will use Excel 365. You can use any available Excel version.
1. Applying Drag and Drop with Mouse to Sort Excel Sheet by Name
Here, we will apply the drag and drop method to manually sort Excel sheet by name.
Steps:
- First, select any sheet then drag the sheet by using a Mouse and drop it where you want.
- Here, we wanted to move the sheet named Mike, therefore, we click and hold the mouse on the sheet named Mike.
- After that, we will see a drop-down arrow on sheet named Mike.
- Then, we will drag the mouse to move the sheet in our desired location.
- After that, we drag the selected sheet after the sheet named Andrew, and drop the sheet by releasing the Mouse.
Next, we can see sheet named Mike has been moved after the sheet named Andrew.
- In a similar way, we dragged all the sheets and sort them according to our needs.
As a result, you will see sorted Excel sheets by name.
2. Inserting VBA to Sort Worksheet Name Alphabetically
In this method, we will insert VBA code to sort Excel sheet by name alphabetically.
Steps:
Here, you can use the keyboard shortcut ALT+F11 to bring the VBA editor window.
A VBA editor window will appear.
- Afterward, from the Insert tab >> select Module.
- After that, in the Module window, we will type the following code.
Sub Sort_Sheetname_Alphabetically()
Dim p As Integer
Dim q As Integer
Dim pResult As VbMsgBoxResult
pResult = MsgBox("Select Yes to Sort Sheeets Alphabetically from A to Z?" & Chr(10) _
& "Select No to sort Sheet Alphabetically from Z to A", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For p = 1 To Sheets.Count
For q = 1 To Sheets.Count - 1
If pResult = vbYes Then
If UCase$(Sheets(q).Name) > UCase$(Sheets(q + 1).Name) Then
Sheets(q).Move after:=Sheets(q + 1)
End If
ElseIf pResult = vbNo Then
If UCase$(Sheets(q).Name) < UCase$(Sheets(q + 1).Name) Then
Sheets(q).Move after:=Sheets(q + 1)
End If
End If
Next q
Next p
End Sub
Code Breakdown
- We declared p,q as Integer and pResult as VbMsgBoxResult
- For MsgBox, we typed “Sort Sheeets Alphabetically from A to Z?” and “Clicking No will sort Sheet Alphabetically from Z to A“
- The first For loop is used to go through the counted Sheets..
- The second For loop is used to look through all worksheets to sort the sheets based on the Alphabet.
- When we select Yes in the MsgBox, the IF statement returns sheets name from A to Z in alphabetic order.
- When we select No in the MsgBox, the IF statement returns sheet name from Z to A in alphabetic order.
- Then, we will click on the Run button to run the code.
Next, a MsgBox will appear.
- After that, we will select Yes.
If you want to sort from Z to A, you have to select No.
- After that, we will close the VBA editor window and go back to Excel sheet.
Therefore, you will see the alphabetically sorted Excel sheet by name.
3. Using Excel VBA to Sort Sheet Names in Ascending Order
The following workbook has 4 Excel sheets. However, the sheet’s names are in irregular order. Here, we will arrange Excel sheet in ascending order of name using VBA.
Steps:
- First, we will follow the steps described in Method-2 to bring the VBA Module window.
- After that, we will type the following code in the Module window.
Sub sort_Sheetname_Ascending()
Dim s, t, u As Double
t = Application.Sheets.Count
For s = 1 To t
For u = 1 To t - 1
If LCase(Sheets(u).Name) > LCase(Sheets(u + 1).Name) Then
Sheets(u).Move after:=Sheets(u + 1)
Next
Next
End Sub
Code Breakdown
- We have declared s,t,u as Double.
- The first For loop is used to look for each worksheet again.
- The second For loop is used to look through all worksheets to sort them in ascending order.
- The IF statement identifies the smaller number sheet name and put it before the larger number sheet name.
- Then, we will click on the Run button to run the code.
- After that, we will close the VBA editor window and go back to Excel sheet.
Therefore, you will see the sorted Excel sheet by name in ascending order.
4. Applying VBA to Sort Excel Sheet Names in Descending Order
The following workbook has 4 Excel sheets. However, the sheet’s names are in irregular order. Here, we will use the VBA to sort Excel sheet by name in descending order.
Steps:
- First, we will follow the steps described in Method-2 to bring the VBA Module window.
- After that, we will type the following code in the Module window.
Sub sort_Sheetname_Ascending()
Dim s, t, u As Double
t = Application.Sheets.Count
For s = 1 To t
For u = 1 To t - 1
If LCase(Sheets(u).Name) > LCase(Sheets(u + 1).Name) Then
Sheets(u).Move after:=Sheets(u + 1)
Next
Next
End Sub
Code Breakdown
- We declared u,v,w as Double.
- The first For loop is used to go through the Counted Sheets.
- The second For loop is used to look through all worksheets to sort the sheets in decending order.
- The IF statement identifies the larger number sheet name and put it before the smaller number sheet name.
- Then, we will click on the Run button to run the code.
- After that, we will close the VBA editor window and go back to Excel sheet.
Finally, you will see the sorted Excel sheet by name in descending order.
Practice Section
In the practice section of your sheet, you can practice the explained methods.
Download Practice Workbook
Conclusion
Here, we tried to show you 4 methods to sort Excel sheet by name. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.
