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

Method 1 – Applying Drag and Drop to Sort an Excel Sheet by Name

Steps:

Select any sheet, drag it using a Mouse, and drop it where you want.
Here, we want to move the sheet named Mike, so we click and hold the mouse on it.
A drop-down arrow named Mike will pop up on the sheet.
Drag the mouse to move the sheet to our desired location.

Drag the selected sheet after the sheet named Andrew, and drop the sheet by releasing the Mouse.

The sheet named Mike has been moved after the sheet named Andrew.

Drag all the sheets and sort them according to your needs.

As a result, you will see sorted Excel sheets by name.

Method 2 – Inserting VBA to Sort a Worksheet Name Alphabetically

Steps:

Go to the Developer tab >> Select Visual Basic to bring the VBA editor window.

You can use the keyboard shortcut ALT+F11 to bring the VBA editor window here.

A VBA editor window will appear.

From the Insert tab >> select Module.

In the Module window, enter 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 Sheets 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 the sheet name from Z to A in alphabetic order.

Click on the Run button to run the code.

A MsgBox will appear.

Select Yes.

If you want to sort from Z to A, select No.

Close the VBA editor window and go back to the Excel sheet.

You will see the alphabetically sorted Excel sheet by name.

Method 3 – Using Excel VBA to Sort Sheet Names in Ascending Order

Steps:

Follow the steps described in Method 2 to bring the VBA Module window.
Enter 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 and sort them in ascending order.
The IF statement identifies the smaller number sheet name and puts it before the larger number sheet name.

Click on the Run button to run the code.
Close the VBA editor window and go back to the Excel sheet.

You will see the sorted Excel sheet by name in ascending order.

Method 4 – Applying VBA to Sort Excel Sheet Names in Descending Order

Steps:

Follow the steps described in Method 2 to bring the VBA Module window.
Enter 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 descending order.
The IF statement identifies the larger number sheet name and puts it before the smaller number sheet name.