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 VB code

Sort worksheets in alphabetical / alphanumeric order with Kutools for Excel

arrow blue right bubble Sort worksheets in alphabetical / alphanumeric order with VB code


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

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

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

VBA: Sort sheets 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

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

doc-sort-sheets6


arrow blue right bubble 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 includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Click Enterprise > Worksheet > Sort Sheets. See screenshot:

Step 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 screenshots:

doc-sort-sheets4
-1
doc-sort-sheets5

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. Click to know more about this utility.


Related article:

Sort worksheet tabs by color


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +13 domclobar
Macro worked perfectly. Thanks for the help and saving me fair amount of time!
2013-11-07 11:23 Reply Reply with quote Quote
Permalink 0 adeoye
HI, I CANT SEEM TO SAVE THE SORTED FILE..ITS PROMPTING TO ENABLE SAVING MACROS...ANY HELP ON THAT?
2016-04-28 10:04 Reply Reply with quote Quote
Permalink +1 Ross
Thanks! Worked like a charm! Greatly appreciate the info!
2014-01-03 21:22 Reply Reply with quote Quote
Permalink +1 Howard
AMAZING! Thank you so much!
2014-01-09 15:40 Reply Reply with quote Quote
Permalink +4 azhar
AMA ZING! Thank you so much
Macro worked perfectly. Thanks for the help and saving me fair amount of time! :D
2014-01-23 08:08 Reply Reply with quote Quote
Permalink 0 Dan
worked like a charm, thanks for the help!!
2014-01-23 18:48 Reply Reply with quote Quote
Permalink +1 Suzen
Macro worked great!!! Thank you so much for the info! My boss was impressed that I fixed the sheets in alpha order so quickly! :lol:
2014-01-28 21:38 Reply Reply with quote Quote
Permalink 0 JA
Baby's first macro, and it worked perfectly. Thanks!
2014-01-30 17:44 Reply Reply with quote Quote
Permalink 0 JZ
The instructions and macro worked great! Why isn't this part of MS Excel by default?
2014-02-14 18:25 Reply Reply with quote Quote
Permalink +1 E-sa
Macro worked great!!! I appreciated all your help.... It is a good thing to teach people who really love to learn... Have a great life...
2014-03-05 23:32 Reply Reply with quote Quote
Permalink 0 Raquel
Macro worked beautifully! Thanks.
:lol:
2014-03-19 15:14 Reply Reply with quote Quote
Permalink 0 Ragal
Awesome :-) Thank you very much VB worked great....
Appreciated...
Ragal
2014-03-27 09:36 Reply Reply with quote Quote
Permalink 0 LauraB
Saved me hours! Thank you!
2014-04-11 15:46 Reply Reply with quote Quote
Permalink 0 Beginner
Works Excellent. Thanks wizard!
2014-04-11 15:54 Reply Reply with quote Quote
Permalink -1 tapendra
amazing help me so much please mail me these kind of things so i can also use in future thanx
2014-04-15 06:04 Reply Reply with quote Quote
Permalink 0 Laurac
Macro worked perfect and I have 75 sheets in this workbook!
2014-04-15 20:41 Reply Reply with quote Quote
Permalink 0 Laurac
Macro worked perfect! and i have 75 sheets in my report!
2014-04-15 20:42 Reply Reply with quote Quote
Permalink 0 JOBY
EXCELLENT MACRO! SALUTE
2014-05-08 10:04 Reply Reply with quote Quote
Permalink 0 Diane Kington
Brilliant . Thanks for sharing this
2014-05-19 10:47 Reply Reply with quote Quote
Permalink 0 sathish
I am getting error Screen Shot 2014-05-19 at 5.43.19 Screen Shot 2014-05-19 at 5.43.19 PM
2014-05-19 12:17 Reply Reply with quote Quote
Permalink 0 Sammi
The Macro works perfectly :lol: no issues. Thank you so much for the information. SUCH a lifesaver!!
2014-06-01 17:30 Reply Reply with quote Quote
Permalink 0 Red Rose
great information ... thanks a lot
2014-06-23 11:47 Reply Reply with quote Quote
Permalink -1 Excel User
it's faster if you sort the names of the sheets in an array and then
move the sheets based on the array.

i.e. after sorting the array

'start with 2 as the first one will be the only one not moved.
For r = 2 To UBound(sheetnam earray)
Sheets(Sheets(s heetnamearray(r )).Name).Move After:=Sheets(s heetnamearray(r - 1))
Next
2014-07-11 00:33 Reply Reply with quote Quote
Permalink 0 Jen
Thank you, thank you, thank you! Love it.
2014-08-04 15:26 Reply Reply with quote Quote
Permalink -1 Dellibabu
this macro really Excellent, thank you very much for giving it
2014-08-07 15:00 Reply Reply with quote Quote
Permalink 0 Ross
Wondering if anyone could help me.
If i had tabs Alpha,Bravo,Cha rlie,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
2014-08-13 14:07 Reply Reply with quote Quote
Permalink +1 Bharat Solanki
excellent work...thanx
2014-08-29 07:20 Reply Reply with quote Quote
Permalink 0 Gwen Banger
Thank you kindly - worked in seconds. Much appreciated - Take Care
2014-09-19 08:45 Reply Reply with quote Quote
Permalink 0 Ricardo Pacheco
I was able to get to the last step.
I pressed F5

and got this error:

Compile Error

Expected End Sub
2014-09-24 17:56 Reply Reply with quote Quote
Permalink 0 yabyaban
Thank you very much in advance
2014-10-03 15:54 Reply Reply with quote Quote
Permalink 0 GaryE
Superb simple macro, worked a treat
2014-10-08 16:22 Reply Reply with quote Quote
Permalink 0 stevedoyle
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() 'Updateby201406 24 Dim xResult As VbMsgBoxResult xTitleId = "KutoolsforExce l"xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton 1, xTitleId) For i = 1 To Application.She ets.Count For j = 1 To Application.She ets.Count - 1 If xResult = vbYes ThenIf UCase$(Applicat ion.Sheets(j).N ame) > UCase$(Applicat ion.Sheets(j + 1).Name) ThenSheets(j).M ove after:=Sheets(j + 1) End IfElseIf xResult = vbNo ThenIf UCase$(Applicat ion.Sheets(j).N ame) < UCase$(Applicat ion.Sheets(j + 1).Name) ThenApplication .Sheets(j).Move after:=Applicat ion.Sheets(j + 1) End IfEnd IfNextNextEnd Sub
2014-10-15 13:31 Reply Reply with quote Quote
Permalink +2 GayeM
Macro worked perfectly. :-)
2015-01-14 02:06 Reply Reply with quote Quote
Permalink 0 mahesh
hi, thanks, worked wonderfully to sort by macros in no time.
much appreciate your tool.
2015-04-01 13:12 Reply Reply with quote Quote
Permalink +1 Madhav
Thanks a lot !!!!!!!!!!!!!
Very very useful tool.
2015-05-06 10:38 Reply Reply with quote Quote
Permalink +1 canuck74
This saved me from having to manually sort worksheets for every company traded on American exchanges - more than 10,000 worksheets. Thanks
2015-06-10 00:59 Reply Reply with quote Quote
Permalink 0 Kishore Joshi
worked like a charm. good work.
2015-06-15 19:46 Reply Reply with quote Quote
Permalink 0 Giang
Very nice :)
Thanks a lot!!!
2015-06-16 06:42 Reply Reply with quote Quote
Permalink 0 Volkan
Thanks a lot !
Very very useful tool.
2015-07-01 06:36 Reply Reply with quote Quote
Permalink 0 Zahid
Thanks a lot
very very nice
once again thanks
2015-08-05 09:20 Reply Reply with quote Quote
Permalink 0 Vamsi
:lol:
Sorting Macro worked perfectly, Thank you
2015-08-26 22:14 Reply Reply with quote Quote
Permalink 0 Akshay
Thanks aton, sorted almost 100 sheets easily! :D
2015-10-26 12:17 Reply Reply with quote Quote
Permalink 0 Alka
Awesome!!!!work ed out great!!!! :-)
2015-10-27 11:04 Reply Reply with quote Quote
Permalink 0 Gary Ho
Marco is fast and Awesome man. :lol:
2015-11-15 10:24 Reply Reply with quote Quote
Permalink 0 Nes
Thank you so much, now i know what to do.. thank you very much
2015-11-25 05:53 Reply Reply with quote Quote
Permalink 0 rachel
thanks tons!!! fantastic macro! and such clear instructions!
2015-12-16 18:00 Reply Reply with quote Quote
Permalink 0 Sam_Mel
Useful instructions... Thanks for adding value toward others knowledge.
2016-02-04 19:02 Reply Reply with quote Quote
Permalink 0 Sidrit Mero
You saved me 3600 seconds ! thank you !
2016-03-24 16:04 Reply Reply with quote Quote
Permalink 0 Miguel
Thank you so much !! It worked great.
2016-04-11 15:19 Reply Reply with quote Quote
Permalink 0 adeoye
hi1 thanks for the code, does it take a long while to load after running the macro
2016-04-28 09:45 Reply Reply with quote Quote
Permalink 0 Dave
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!
2016-05-16 22:49 Reply Reply with quote Quote
Permalink 0 Wahid
Work great, many thanks!!
2016-08-04 06:14 Reply Reply with quote Quote
Permalink 0 kristine
I love this macro! I want to modify the code to exclude certain sheets. Please help
2016-08-10 18:39 Reply Reply with quote Quote
Permalink 0 Rosie
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...
2016-08-23 16:05 Reply Reply with quote Quote
Permalink 0 Wasim Aftab
Thanks,
It works Great.
2016-10-05 04:33 Reply Reply with quote Quote
Permalink 0 Ming
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.
2016-10-13 13:36 Reply Reply with quote Quote
Permalink 0 John Machin
Macro worked first time, thank -you
2016-10-17 00:33 Reply Reply with quote Quote
Permalink 0 Aziz
Thank you a lot many many thanks
2016-11-15 06:47 Reply Reply with quote Quote
Permalink 0 josphat
This is amazing. Perfect. Thank you
2016-11-23 10:37 Reply Reply with quote Quote
Permalink 0 racsar
Omg, such a time saver!
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 37 worksheets. 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.
2016-12-29 04:44 Reply Reply with quote Quote
Permalink 0 Peggy
excellent!!! this saved me a lot of time!
2017-01-04 20:36 Reply Reply with quote Quote
Permalink 0 Terri A. Lapwing
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
2017-02-16 13:49 Reply Reply with quote Quote
Permalink 0 Jae
Woohoo! Thanks so very much!!!!! :lol:
2017-02-16 15:19 Reply Reply with quote Quote
Permalink 0 liezl
yes! you help me a lot. big thanks :-)
2017-02-20 01:33 Reply Reply with quote Quote

Add comment


Security code
Refresh