How to Sort Excel Sheet by Name (4 Easy Methods)

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.

Download Practice Workbook


4 Methods to Sort Excel Sheet by Name

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.

How to Sort Excel Sheet by Name


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 name Andrew, and drop the sheet by releasing the mouse.

How to Sort Excel Sheet by Name

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 Sheet Name Alphabetically

In this method, we will insert VBA code to sort Excel sheet by name alphabetically.

Steps:

  • First, we will go to the Developer tab >> select Visual Basic to bring the VBA editor window.

Here, you can use the keyboard shortcut ALT+F11 to bring the VBA editor window.

How to Sort Excel Sheet by Name

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 shhets 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 VBA to Sort Sheet Names in Ascending Order

The 3lowing 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 ascending order.

How to Sort Excel Sheet by Name

Steps:

  • First, we will follow the steps describe 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. Use of VBA to Sort in Descending Order

The following workbook has 4 Excel sheets. However, the sheet’s name 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 describe 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

How to Sort Excel Sheet by Name

Code Breakdown

  • We decalred 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.

How to Sort Excel Sheet by Name


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. Please visit our website Exceldemy to explore more.

Afia
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo