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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 named 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 Worksheet 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 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.

How to Sort Excel Sheet by Name

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

How to Sort Excel Sheet by Name

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.

How to Sort Excel Sheet by Name


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.


Related Article


<< Go Back to Organize Sheets in Excel | Excel Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo