How to Sort Excel Tabs in Ascending or Descending Order: 2 Methods

Method 1 – Sorting Sheet Tabs Manually in Excel

STEPS:

Click on the tabs you want to move.
Drag the tab left or right by clicking on the left mouse button.

There you go.

But you have to do it for each and every tab.

TIPS: Drag tabs around, hold down the Ctrl key on the keyboard. This will produce a copy of the tabs rather than moving them.

Method 2 – Using VBA to Sort Excel Tabs

2.1. Sort Excel Sheet Tabs Alphabetically from A to Z

STEPS:

Go to the Developer tab on the ribbon.
Click on Visual Basic to open the Visual Basic Editor where we will write the VBA codes.
Another way to open the Visual Basic Editor is simply to press Alt + F11.

Instead of opening the editor from the Developer tab, you can click on any sheet on your spreadsheet and then right-click. Select the View Code option.

Open up the visual basic window.
Go to Insert and select Module from the drop-down menu.

Suggestion: You can’t write the code on any sheet. You must need to insert a Module to write the code as we are going to use the code for the whole spreadsheet, not only any specific sheet.

Write any code for any specific sheet, only then may you use the sheets to write the codes there.

Copy and paste the VBA code below.

VBA Code:

Sub Sort_AtoZ()
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
            Sheets(j).Move after:=Sheets(j + 1)
        End If
    Next
Next
End Sub

Press the F5 key or click on the Run Sub button to run the code.

Output:

This VBA Macro sorts the tabs in the current workbook in ascending alphabetical order, starting with worksheets whose names begin with digits and then moving on to tabs beginning with A and ending with Z.

2.2. Excel Sheet Tabs Sorting from Z to A

STEPS:

The previous method, to open the Visual Basic Editor, first go to the Developer tab on the ribbon.
Click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.

Another way to open the Visual Basic Editor is right-click on any sheet and select View Code.

Go to Insert and select Module from the drop-down menu.

Write down the VBA Code below.

VBA Code:

Sub Sort_ZtoA()
  For i = 1 To Application.Sheets.Count
      For j = 1 To Application.Sheets.Count - 1
          If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                Application.Sheets(j).Move after:=Application.Sheets(j + 1)
          End If
     Next
Next
End Sub