Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to sort worksheets in alphabetical / alphanumeric order in Excel?

Normally you can sort or arrange worksheet tabs order in Excel by dragging and dropping the sheet tabs on the sheet tab bar. But for getting this done with multiple worksheets, you may consider the following tricky ways to quickly sort worksheets in alphabetical / alphanumeric order in a large workbook.

Sort worksheets in alphabetical / alphanumeric order with VBA code

Sort worksheets in alphabetical / alphanumeric order with Kutools for Excel


Quickly sort all worksheers in alphabetical/alphanumeric order in Excel:

The Sort Sheets utility of Kutools for Excel can help you quickly sorting all worksheets in alphabetical or alphanumeric order in Excel with only several clicks. Download the full feature 60-day free trail of Kutools for Excel now!

doc-sort-sheets6

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Sort worksheets in alphabetical / alphanumeric order with VBA code

There is a macro for sorting worksheets by alpha posted in the Microsoft Support center. We can apply it with following steps:

1.  Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2.  Click Insert > Module, and then paste the following macro in the Module Window.

VBA: Sort sheets in alphabetical / alphanumeric order

Sub SortWorkBook()
'Updateby20140624
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3.  Press the F5 key to run this macro. In the following prompt box, click Yes, all the worksheets will be sorted by ascending alphabetical order; and click No, all the worksheets will be sorted by descending alphabetical order.


Sort worksheets in alphabetical / alphanumeric order with Kutools for Excel

If you are not familiar with macros or prefer to other ways, you can try Kutools for Excel. Kutools for Excel's Sort Sheets tool can sort all worksheets easily.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

1. Click Kutools Plus > Worksheet > Sort Sheets. See screenshot:

2. In the Sort Sheets dialog box, select one sorting type that you need on the right pane, such as Alpha Sort, Alpha Numeric Sort, and then click the OK button. See screenshot:

Then all worksheets are sorted based on the specified sorting type. See screenshot:

doc-sort-sheets6

Kutools for Excel's Sort Sheets tool can quickly rearrange all worksheets in the active workbook. It supports several sorting types, including Alpha Sort, Alpha Numeric Sort, Color Sort and Reverse. In addition, you can also move up / down worksheets, and reset the sorting.

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel


Demo: Sort all worksheets in alphabetical / alphanumeric order

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related article:

Sort worksheet tabs by color


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mahir · 9 months ago
    Thank you for your help, very much appreciated...
  • To post as a guest, your comment is unpublished.
    Sarah · 1 years ago
    I don't know VBA at all but your instructions worked perfectly. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Mahir · 1 years ago
    Thank you :)
  • To post as a guest, your comment is unpublished.
    SAM · 1 years ago
    Very helpful :) Thank you!
  • To post as a guest, your comment is unpublished.
    Sagar · 1 years ago
    Thank you sooooooo much !!!
  • To post as a guest, your comment is unpublished.
    Varun Shah · 1 years ago
    Thanks a Lot!!!
  • To post as a guest, your comment is unpublished.
    Chris Hall · 1 years ago
    I so appreciate this ... thank you...thank you...Thank You!~chrissy
  • To post as a guest, your comment is unpublished.
    Roxanne · 1 years ago
    I love you right now. Thank you!
  • To post as a guest, your comment is unpublished.
    mozay · 2 years ago
    thanks so much guys this saved me alot of time
  • To post as a guest, your comment is unpublished.
    eka · 2 years ago
    Thanks so much..
  • To post as a guest, your comment is unpublished.
    liezl · 2 years ago
    yes! you help me a lot. big thanks :-)
  • To post as a guest, your comment is unpublished.
    Jae · 2 years ago
    Woohoo! Thanks so very much!!!!! :lol:
  • To post as a guest, your comment is unpublished.
    Terri A. Lapwing · 2 years ago
    Good morning, On an Excel spread sheet, you can group columns of data. They condense and are represented by a + sign; if you want to expand them, a line appears along with the - sign. Can you perform a similar task with worksheets inside of an Excel workbook. I would like to condense a set of worksheets of a particular fiscal quarter.
    Please help,
    Terri
  • To post as a guest, your comment is unpublished.
    Peggy · 2 years ago
    excellent!!! this saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    racsar · 2 years ago
    [b]Omg, such a time saver! [/b]
    I have a list to keep track of which books I've read by favorite serial-writer authors. A different worksheet for each author got plugged-in very haphazardly as I discovered new authors differentiating them only by tab colors. Over the years it has grown to [b]37 worksheets[/b]. For some reason it just occurred to me today that it would be darn nice to have them in alphabetical order. A task which seemed daunting when I thought of pushing them around by hand.
    I have an old 2003 vs of Excel, running in Windows 10. Also, I'm no Tech, I'm completely autodidactic, so attempting anything of this sort (for me) is always risky. Amazingly, it worked like a charm.
    THANK YOU, so much!

    I have bookmarked you, I WILL be back.
  • To post as a guest, your comment is unpublished.
    josphat · 2 years ago
    This is amazing. Perfect. Thank you
  • To post as a guest, your comment is unpublished.
    Aziz · 2 years ago
    Thank you a lot many many thanks
  • To post as a guest, your comment is unpublished.
    John Machin · 3 years ago
    Macro worked first time, thank -you
  • To post as a guest, your comment is unpublished.
    Ming · 3 years ago
    Thanks for the vba codes, it works1

    One suggestion:
    After the dim comment add the following:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Before the end sub comment, add the following
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    This will speed up the process.
  • To post as a guest, your comment is unpublished.
    Wasim Aftab · 3 years ago
    Thanks,
    It works Great.
  • To post as a guest, your comment is unpublished.
    Rosie · 3 years ago
    This macro worked great for me for a while! I have been using it in a huge spreadsheet at work, and it has been great for keeping it organized as I am continually adding new tabs. However, today I got this error message:

    "Excel was able to open the file by repairing or removing the unreadable content. Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))"

    Now the macro is gone. Does anyone know why this happened or how I can fix it? I'd like to be able to keep using this macro without issues...
  • To post as a guest, your comment is unpublished.
    kristine · 3 years ago
    I love this macro! I want to modify the code to exclude certain sheets. Please help
  • To post as a guest, your comment is unpublished.
    Wahid · 3 years ago
    Work great, many thanks!!
  • To post as a guest, your comment is unpublished.
    Dave · 3 years ago
    How can I force an alpha sort in which lower case follows upper case?
    I tried a Custom List, which accomplished nothing, realized I hadn't selected Options >> Case Sensitive and tried again, with almost the same results.
    Only difference was that Excel sorted individual lower case entries before identical upper case ones.
    What I want is: ABCDE ... WXYZabcde ... wxyz

    Thanks!
  • To post as a guest, your comment is unpublished.
    adeoye · 3 years ago
    hi1 thanks for the code, does it take a long while to load after running the macro
  • To post as a guest, your comment is unpublished.
    Miguel · 3 years ago
    Thank you so much !! It worked great.
  • To post as a guest, your comment is unpublished.
    Sidrit Mero · 3 years ago
    You saved me 3600 seconds ! thank you !
  • To post as a guest, your comment is unpublished.
    Sam_Mel · 3 years ago
    Useful instructions...Thanks for adding value toward others knowledge.
  • To post as a guest, your comment is unpublished.
    rachel · 3 years ago
    thanks tons!!! fantastic macro! and such clear instructions!
  • To post as a guest, your comment is unpublished.
    Nes · 3 years ago
    Thank you so much, now i know what to do.. thank you very much
  • To post as a guest, your comment is unpublished.
    Gary Ho · 3 years ago
    Marco is fast and Awesome man. :lol:
  • To post as a guest, your comment is unpublished.
    Alka · 4 years ago
    Awesome!!!!worked out great!!!! :-)
  • To post as a guest, your comment is unpublished.
    Akshay · 4 years ago
    Thanks aton, sorted almost 100 sheets easily! :D
  • To post as a guest, your comment is unpublished.
    Vamsi · 4 years ago
    :lol:
    Sorting Macro worked perfectly, Thank you
  • To post as a guest, your comment is unpublished.
    Zahid · 4 years ago
    Thanks a lot
    very very nice
    once again thanks
  • To post as a guest, your comment is unpublished.
    Volkan · 4 years ago
    Thanks a lot !
    Very very useful tool.
  • To post as a guest, your comment is unpublished.
    Giang · 4 years ago
    Very nice :)
    Thanks a lot!!!
  • To post as a guest, your comment is unpublished.
    Kishore Joshi · 4 years ago
    worked like a charm. good work.
  • To post as a guest, your comment is unpublished.
    canuck74 · 4 years ago
    This saved me from having to manually sort worksheets for every company traded on American exchanges - more than 10,000 worksheets. Thanks
  • To post as a guest, your comment is unpublished.
    Madhav · 4 years ago
    Thanks a lot !!!!!!!!!!!!!
    Very very useful tool.
  • To post as a guest, your comment is unpublished.
    mahesh · 4 years ago
    hi, thanks, worked wonderfully to sort by macros in no time.
    much appreciate your tool.
  • To post as a guest, your comment is unpublished.
    GayeM · 4 years ago
    Macro worked perfectly. :-)
  • To post as a guest, your comment is unpublished.
    stevedoyle · 5 years ago
    Hi, I'm trying to run the macro but keep getting the error as follows:

    expected end of statement.

    The Macro is as follows:
    ysg2y
    VBA: Sort sheets order.

    12345678910111213141516171819 Sub SortWorkBook() 'Updateby20140624 Dim xResult As VbMsgBoxResult xTitleId = "KutoolsforExcel"xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId) For i = 1 To Application.Sheets.Count For j = 1 To Application.Sheets.Count - 1 If xResult = vbYes ThenIf UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) ThenSheets(j).Move after:=Sheets(j + 1) End IfElseIf xResult = vbNo ThenIf UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) ThenApplication.Sheets(j).Move after:=Application.Sheets(j + 1) End IfEnd IfNextNextEnd Sub
  • To post as a guest, your comment is unpublished.
    GaryE · 5 years ago
    Superb simple macro, worked a treat
  • To post as a guest, your comment is unpublished.
    yabyaban · 5 years ago
    Thank you very much in advance
  • To post as a guest, your comment is unpublished.
    Ricardo Pacheco · 5 years ago
    I was able to get to the last step.
    I pressed F5

    and got this error:

    Compile Error

    Expected End Sub
  • To post as a guest, your comment is unpublished.
    Gwen Banger · 5 years ago
    Thank you kindly - worked in seconds. Much appreciated - Take Care
  • To post as a guest, your comment is unpublished.
    Bharat Solanki · 5 years ago
    excellent work...thanx
  • To post as a guest, your comment is unpublished.
    Ross · 5 years ago
    Wondering if anyone could help me.
    If i had tabs Alpha,Bravo,Charlie,Delta, Echo etc and i wanted to sort the tabs according to a list. lets say list contains Alpha, Charlie, Echo..is there anyway to do this? and the remaining tabs to alight behind the listed tabs in alphabetical order?

    thanks
  • To post as a guest, your comment is unpublished.
    Dellibabu · 5 years ago
    this macro really Excellent, thank you very much for giving it