How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)

In Microsoft Excel, If we wish to sort excel tabs, there are no built-in functions or any 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.


Download Practice Workbook

You can download the workbook and practice with them.


2 Ways to Sort Excel Tabs in Ascending or Descending Order

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. But the tabs of the dataset do not have any arrangement. Let’s see how to sort them simply.


1. Sort 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.

Sort Sheet Tabs Manually in Excel

  • And, there you go!

Sort Sheet Tabs Manually in Excel

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.

Related Content: How to Use Advanced Sorting Options in Excel


Similar Readings:


2. Use 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.

Sort Excel Sheet Tabs Alphabetically from A to Z

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

Sort Excel Sheet Tabs Alphabetically from A to Z

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

Sort Excel Sheet Tabs Alphabetically from A to Z

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


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.

Sort Excel Sheet Tabs Alphabetically from A to Z

  • 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: VBA to Sort Table in Excel (4 Methods) 


Conclusion

The above methods assist you to sort 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.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo