How to alphabetize tabs in Excel

The tutorial shows how you can quickly sort Excel worksheets in alphabetical order by using VBA code and the Workbook Manager tool.

Microsoft Excel provides a number of quick and easy ways to arrange columns or rows in alphabetical order. But there is only one method to rearrange worksheets in Excel - drag them to the desired position on the sheet tab bar. When it comes to alphabetizing tabs in a really large workbook, this may be a long and erroneous way. Looking for a time-saving alternative? There exist only two: VBA code or third-party tools.

How to alphabetize tabs in Excel with VBA

Below you will find three VBA code examples to sort Excel sheets ascending, descending, and in either direction based on the user's choice.

Implying that you have some experience with VBA, we will only outline the basic steps to add a macro to your worksheet:

  1. In your Excel workbook, press Alt + F11 to open the Visual Basic Editor.
  2. On the left pane, right-click ThisWorkbook, and then click Insert > Module.
  3. Paste the VBA code in the Code window.
  4. Press F5 to run the macro.

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Tip. If you want to keep the macro for further usage, be sure to save your file as an Excel macro-enabled workbook (.xlsm).

Alternatively, you can download our sample Alphabetize Excel Tabs workbook, enable content if prompted, and run the desired macro directly from there. The workbook contains the following macros:

  • TabsAscending - sort sheets alphabetically from A to Z.
  • TabsDescending - arrange sheets in the reverse order, from Z to A.
  • AlphabetizeTabs - sort sheet tabs in both directions, ascending or descending.

With the sample workbook downloaded and open in your Excel, open your own workbook where you want to alphabetize tabs, press Alt + F8, select the desired macro, and click Run.

Sort Excel tabs alphabetically from A to Z

This little macro arranges the sheets in the current workbook in ascending alphanumeric order, first worksheets whose names start with numbers, then sheets from A to Z.

Sub TabsAscending() 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 MsgBox "The tabs have been sorted from A to Z." End Sub

Arrange Excel tabs from Z to A

If you want to sort your sheets in descending alphanumeric order (Z to A, then sheets with numeric names), then use the following code:

Sub TabsDescending() 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 MsgBox "The tabs have been sorted from Z to A." End Sub

Alphabetize tabs ascending or descending

This macro lets your users decide how to sort worksheets in a given workbook, alphabetically from A to Z or in the reverse order.

Since the standard dialog box (MsgBox) in Excel VBA only allows choosing from a handful of predefined buttons, we will create our own form (UserForm) with three custom buttons: A to Z, Z to A, and Cancel.

For this, open the Visual Basic Editor, right-click ThisWorkbook, and click Insert > UserForm. Name your form SortOrderFrom, and add 4 controls to it: a label and three buttons:
Create a UserForm to alphabetize Excel tabs ascending or descending.

Next, press F7 (or double-click the form) to open the Code window and paste the below code there. The code intercepts button clicks and assigns a unique tag to each button:

Private Sub CommandButton1_Click() Me.Tag = 1 Me.Hide End Sub Private Sub CommandButton2_Click() Me.Tag = 2 Me.Hide End Sub Private Sub CommandButton3_Click() Me.Tag = 0 Me.Hide End Sub

Depending on whether the user clicks the A to Z or Z to A button on your form, sort tabs in ascending alphabetical order (selected by default) or descending alphabetical order; or close the form and do nothing in case of Cancel. This is done with the following VBA code, which you insert in the usual way via Insert > Module.

Sub AlphabetizeTabs() Dim SortOrder As Integer SortOrder = showUserForm If SortOrder = 0 Then Exit Sub For x = 1 To Application.Sheets.Count For y = 1 To Application.Sheets.Count - 1 If SortOrder = 1 Then If UCase$(Application.Sheets(y).Name) > UCase$(Application.Sheets(y + 1).Name) Then Sheets(y).Move after:=Sheets(y + 1) End If ElseIf SortOrder = 2 Then If UCase$(Application.Sheets(y).Name) < UCase$(Application.Sheets(y + 1).Name) Then Sheets(y).Move after:=Sheets(y + 1) End If End If Next Next End Sub Function showUserForm() As Integer showUserForm = 0 Load SortOrderForm SortOrderForm.Show (1) showUserForm = SortOrderForm.Tag Unload SortOrderForm End Function

If you are not very comfortable with VBA yet, you can simply download our Sample Workbook to Alphabetize Tabs, open it in your Excel alongside your own file where you want to sort tabs, and run the AlphabetizeTabs macro from your workbook:
Run the macro to sort Excel tabs alphabetically or in the reverse order.

Choose the preferred sort order, say, A to Z, and observe the results:
Choose to arrange the sheet tabs alphabetically from A to Z.

Tip. With VBA, you can also create copies of your Excel worksheets. The code is available here: How to duplicate sheet in Excel with VBA.

How to sort Excel tabs alphabetically with Ultimate Suite

The users of our Ultimate Suite for Excel don't have to fiddle around with VBA - they have a multi-functional Workbook Manager at their disposal:
Workbook Manager for Excel

With this tool added to your Excel ribbon, alphabetizing tabs is done with a single button click, exactly as it should be!
Alphabetize tabs in Excel with a button click.

If you are curious to explore this and 70+ more professional tools for Excel, a trial version of our Ultimate Suite is available for download here.

I thank you for reading and hope to see you on our blog next week!

Table of contents

26 comments

  1. Hi I want to sort excel sheets alphabetically by surname, where each sheet is called: First name Last name e.g. Louise OLoughlin or Mary Murphy
    Is it possible to sort by surname and not the first name as I have them sorted by first name but want to sort by surname

    many thanks
    kind regards
    Louise

  2. It worked great for me as well, however, when it was completed I noticed that it deleted one of the sheets in the process.

    • Hello Brian!
      The VBA code suggested above only moves the worksheets, but does not delete any of them. You can look through the code carefully, and you will not find "Delete" there.

  3. Thanks - works perfectly!

  4. I love the tab alphabetize utility module, but I am wondering if there is any way to have it start alphabetizing after tab four. The first four tabs are some summary tabs and I do not want them to sort with the other tabs.

    • I suggest using Workbook Manager. Create a new book in Workbook Manager, move the sheets that do not need to be sorted into it. Then sort the remaining sheets. Then move the unsorted sheets back to the original book. For a detailed description of Workbook Manager, see the article above and this link.

    • You can also just move the "Y" variable to a higher number as this is the starting point for the sort function.

      For example:

      "For Y = 1 To Application.Sheets.Count - 1"

      You could change this to

      "For Y = 10 To Application.Sheets.Count - 1"

      If you wanted to start on the 10th sheet(tab), it will then start sorting from the 10th tab and up and will not mess with the first 9 sheets(tabs).

  5. Thank you...worked like a charm.

  6. thank you so much. worked like a champ. saved my precious time.

  7. Thank you so much! This was a great help to me!

  8. Is there a way we can group tab, I am thinking of coloring the TABS but I am wondering if there is a much better way.. Then do a bubble sort TAB COLOR then Alphabetical?

    I will appreciate any advice on this

  9. Thank you very much for the TAB sorting method, it helped me a lot !

  10. Is there a way to do this with dates? My tabs are labeled like 12-2-20, 7-5-20, ETC. I used the W/E date. I would like to reverse them so that I can add at the beginning rather than the end.

  11. What if i just want to sort ascending/descending a selected group of tabs not all the tabs in a workbook? Is there a way to do it?

  12. Thanks a lot for such a wonderful macro. I want to extend this macro by excluding a few tabs (2 to be precise) from sorting. I want these two to be always in the beginning without changing their sequence? Is it possible? How can I do that?

    Regards,

    Rahul Dev

  13. I just started using the sort tabs macro, it works great and saves a lot of time. I have some workbooks that I only want to sort certain worksheets, I was just wondering if there is a way to only select and sort certain worksheets in a workbook?

    Thank you, in advance for any help.

  14. Hello,
    First off, very useful information worked perfectly; however, the sheets I'm organizing download from a report generated online. This report has generic tab names "summary 1" etc.

    Is there a way to sort the tabs based off of the data in a cell. For example my report has generic tab names, but in cell C3 there is a person's name "David Mondoux". I am hoping that I can sort the tabs alphabetically based on the data in that cell.

    Any help would be EXTREMELY time-saving and appreciated.

  15. I just alphabetized my first workbook following your instructions...
    Thank you it worked flawlessly!

  16. Oh my gosh, this is great! Thank you so much! So easy!

  17. Great information there! Is it at all possible to organise sheets in descending date order? Any help would be appreciated.

    Thanks
    Lucas

  18. Thank you so much, saved me so much time!!

  19. i want to copy the headi of main sheet that sheet 01. and paste that heading multyple sheets with numarical order
    eg: heading work id 1053(sheet 01) copy this heading and paste this heading in every pages. but the sheet name is renamed automaticaly that sheet 02,sheet 03 sheet 04 etc in every sheets automaticaly..pls help me

  20. I created a table with the letter "G" in certain cells (one column).
    I'm trying to get a sum of the number of cells with the letter "G".
    Also needing a sum of the cells with the letter "G" that are green in color.

    I have tried several samples I found at your website; ablebits.com with no success.

    Thank you.

    • Kimosabe:
      Here's how to count the cells that contain "G":
      =COUNTIF(A3:A35,"G") where the G cells are in the range A3 to A35.
      To count the formatted cells you'll need to look at the above discussion and get the VBA solution.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)