How to Sort Excel Tabs in Ascending or Descending Order

In Microsoft Excel, If we wish to sort Excel tabs, there are no built-in functions or tools to do that. We can only do it manually or using macros can help. In this article, we will learn some VBA macros to sort tabs in Excel and also have a look at how we can sort them manually.

While working with a lot of tabs in Excel, if the tabs have an arrangement, it would be easy to find the tab. To sort tabs in Excel quickly, we are going to use the dataset below. However, the tabs of the dataset do not have any arrangement. Let’s see how to sort them simply.

1. Sorting Sheet Tabs Manually in Excel

In Excel, there are no built-in functions or formulas, or any tools to sort tabs/sheets. Manually sorting the tabs may be time-consuming. Let’s follow the steps to sort tabs manually.

STEPS:

• First, click on the tabs you want to move.
• Second, drag the tab left or right by clicking on the left mouse button.

• And, there you go!

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

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

Read More: How to Perform Custom Sort in Excel

2. Using VBA to Sort Excel Tabs

Excel VBA helps to automate the task and execute various functions or formulas. Excel VBA makes everyday activities less tedious. With VBA Macros, we can create custom user-generated functions and automate manual operations to save time and effort. With Excel VBA we can easily sort tabs in ascending or descending order as per our wish.

2.1. Sort Excel Sheet Tabs Alphabetically from A to Z

To sort tabs in ascending order we can use the VBA code which will sort the tabs alphabetically from A to Z. Let’s demonstrate the procedure of how we can use VBA Macros to sort tabs in ascending order.

STEPS:

• Firstly, go to the Developer tab on the ribbon.
• Secondly, 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.

• Or, 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.

• And, this will open up the visual basic window.
• Next, 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.

When we need to write any code for any specific sheet only then you may use the sheets to write the codes there.

• After that, 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

• Next, 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.

Read More: How to Do Advanced Sorting in Excel

2.2. Excel Sheet Tabs Sorting from Z to A

To sort tabs in descending order, we can use the VBA code which will sort the tabs alphabetically from Z to A. Let’s follow the steps below to sort tabs in descending order.

STEPS:

• Likewise, the previous method, to open the Visual Basic Editor, first go to the Developer tab on the ribbon.
• Next, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.

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

• Next, go to Insert and select Module from the drop-down menu.

• Now, 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

• Finally, Run the code by clicking the Run Sub button, on the other hand, press the keyboard shortcut F5 key to run the code.

Output:

This will organize the tabs in descending alphabetical order.

Read More: How to Perform Random Sort in Excel

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

The above methods assist you in sorting Excel tabs. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy blog!

Related Articles

• How to Sort IP Address in Excel
• Advantages of Sorting Data in Excel
• How to Sort and Filter Data in Excel
• Difference Between Sort and Filter in Excel
• How to Sort Drop Down List in Excel

<< Go Back to Sort in Excel | Learn Excel