How to Sort Sheets in Excel

You’re plowing through a project in Excel.  You’re averaging and adding and dividing and returning depreciations like nobody’s business.  You’re inserting new sheets and naming the tabs without even thinking about it.  Suddenly, it occurs to you that maybe, just maybe, it would be cool if those tabs were in alphabetical order.  No sweat, you think to yourself.  I’ll just mosey on over to the Data menu, select Sort and. . .hmmm.  It has to be in here somewhere.  Well, I AM working with sheets, you remind yourself.  Maybe if I just right-click one of these here tabs. . .  Nope, not there either.  You head on over to the Help section, I mean, one can’t be expected to know everything in Excel, right?  Hmmm.  No answer there, either. 

Annoyance starts to set in at this point.  Why on earth, you ask yourself, is it so #&@$ing hard to sort my worksheets?

Newsflash: Excel does not provide an option to sort sheets alphanumerically!  I don’t know what manner of insanity this is, but there is hope.  You can create a macro and sort sheets in ascending or descending or until your heart’s content.

1.  Click Tools –> Macro –> Macros.

2.  Name the Macro (Remember, no spaces! Try SortSheets as a name).

3.  Click the Create button.  This will open the Visual Basic editor.

4.  Highlight the text in the code window.  It should read ‘Sub SortSheets() End Sub’

5.  Copy and replace the highlighted text with the following code:

Sub Sort_Active_Book()  
Dim i As Integer  
Dim j As Integer  
Dim iAnswer As VbMsgBoxResult  
'  
' Prompt the user as which direction they wish to  
' sort the worksheets.  
'  
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _  
     & "Clicking No will sort in Descending Order", _  
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")  
   For i = 1 To Sheets.Count  
      For j = 1 To Sheets.Count - 1  
'  
' If the answer is Yes, then sort in ascending order.  
'  
         If iAnswer = vbYes Then  
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then  
               Sheets(j).Move After:=Sheets(j + 1)  
            End If  
'  
' If the answer is No, then sort in descending order.  
'  
         ElseIf iAnswer = vbNo Then  
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then  
               Sheets(j).Move After:=Sheets(j + 1)  
            End If  
         End If  
      Next j  
   Next i  
End Sub

5.  Click File --> Close and return to Microsoft Excel.

Now run your macro by clicking Tools –> Macro –> Macros.  Highlight Sort_Active_Book and click Run.  When prompted, select Yes or No to sort your sheets in ascending or descending order. 

Nifty, huh?

The macro will only be available in the active workbook.  There are ways to create a template, save it to the XLStart menu and have the macro be available for all your workbooks, but you’d have to lower your macro security level or digitally sign your macro.

See the following Microsoft Office Assistance and Knowledge Base (MS KB) articles for my sources and more information:

How to Sort Sheets in a Workbook

How to run the sample code for the Office XP programs from Knowledge Base articles

About Excel templates

Happy sorting from a random broad.

Good luck and happy taking care of things from a random broad.

Add to Google

About these ads
Explore posts in the same categories: Excel, How to, Microsoft, Microsoft Knowledge Base, Random Thoughts, Sorting, Templates

85 Comments on “How to Sort Sheets in Excel”

  1. Donna Says:

    Wow, thanks, was so easy to do. Thanks so much!

  2. Brad Says:

    Thats quite good but when trying to sort workbooks with a few hundred sheets it takes forever – If there is a more efficient macro I’d like to see it. — Then again, the computer i’m using is a piece of crap!

  3. Nick Says:

    The macro shown is very good, however I would like you to help me by also sorting by tab colour…
    I have a large excel workbook, with about 90 sheets, I have colour coded, all the sheets, the names of the tabs are numbers, is it possible to create a macro to sort by tab colour, and then by tab name, keeping the tabs in colour blocks ???
    At this time I can only find macro’s that sort by name or colour

  4. Nick Says:

    If you add ‘Application.ScreenUpdating = False’, to the macro, under the DIM statements, the speed of the macro is inproved greatly, exspecialy if you have a large number of sheets…

  5. Alex Says:

    FWIW, bubble sort is the slowest (but the easiest to implement) algorithm out there. If you have hundreds of sheets, you might want to code a faster one in.

  6. Alex Says:

    But I just copied, pasted, and loved your code, so still a big thanks!

  7. Andy Boyles Says:

    You know, this is just great stuff. It’s amazing what Google and a knowledgeable, generous person who contributed this macro to the public domain can do.

    Thanks very much – I used it first time and it worked like a charm!!!!

  8. Courtney Says:

    Incredibly helpful!! I was so frustrated with Excel until I found this!!
    Thanks, thanks, thanks!

  9. Chris Says:

    WOW!!!! Unbelievable, saved me a ton of time. Thank you.

  10. dsine Says:

    thanks a lot. saved me hours of pain.

  11. carlos manzano Says:

    Fantástico, tengo una planilla con 2800 hojas, Maravilloso!!!


  12. Thank you a million times, this was so cool! You saved me a lot of time, I had to spend the whole week end sorting those sheets manually and would have missed the Friday beers and barbeque. LoL, Thanx!!

  13. Tom Says:

    That has to be one of the most useful and easy to install Macros I have found.. Thank You =o)

  14. Bob Says:

    Hey Hey!

    Thanks so much for this, this is wonderful!

    What a gift to us all.

    Cheers,

    Bob

  15. Lost Fan Says:

    Thanks a million! million million….. i had like a zillion sheets to sort! it was so cool!!!!
    thanks!

  16. Nick Says:

    That was awesome. I had 50+ tabs to sort and it worked like a charm. I appreciate the posting.

  17. Felippe Says:

    oh, thanks
    very useful

  18. Michael Says:

    I wanted to echo earlier comments by Andy Boyles.
    I knew someone would know how to do this and am truly grateful for your help!!

  19. Jeep Says:

    Wow, thanks. I’ve never done a Macro before and it was easy – thanks to you!

  20. paul Says:

    Brilliant. Thanks.

  21. Shawna Says:

    Thanks! I love you! I found many other instructions, but I couldn’t understand them. Yours was easy to follow.

  22. Sho Says:

    Thx, that was a piece of cake, soooooooo helpful.

  23. candy Says:

    How can we know when a macro is safe? I know they can contain viruses. Can they be scanned?

  24. Everett Says:

    WoW this was very helpful!!!!!!!!

  25. Liz Says:

    Thank you so much!!! Nobody could help me and it was very frustrating. So glad I can finally do it.

  26. austin Says:

    This is effin great you rock!!

  27. Peter King Says:

    Hi there,
    it does work, but does anybody know you can just copy and paste your complete programme rather than typing it all out.
    Just make sure you miss out the last line (No 5)
    There was a lot (Copy/Paste) in video basic which saved a lot of time and avoided mistakes,
    Peter

  28. croeski Says:

    That was amazing!!! You just saved me hours!!! I will be going and buying a macro book tonight. Awesome! Now, do you know how to get excel to sort like the following… 1,2, 3, 11 instead of 1, 11, 2, 3???

    • Vikas Says:

      To achieve that .. you have to number your sheets as such … 01, 02, 03 … 11 … so on (if the number is less than 100). I doubt people working on sheets larger than that .. however if that happens to be the case .. you have to follow the 001, 002 .. 011, 022 … numbering pattern. Also that was a cool macro.

    • Lynda Says:

      You saved my life by asking this..I’m so happy!!

  29. Kothainayaki K Says:

    Thanks so much for this macro.
    It helped me a lot. Realized that Macros are amazing. :)

  30. Alexander Nevermind Says:

    THANK YOU!!!! I am in the office at 7:30pm the night before Thanksgiving and you just saved me an hour easy!!!

  31. Sheri Says:

    So cool! Thanks. Love copy & paste.


  32. Looks like you are a real pro. Did ya study about the subject? lawl

  33. Heather Says:

    Thank you! Thank you! Thank you!

  34. martin Says:

    SWEEEEEEET! tack som fan.

  35. R Goodrich Says:

    I’m not familiar with macros. What do you mean prompt the user as which directed they wish to sort the worksheets. I’m not following what to put in. Thank you.

    • Danny Says:

      You don’t have to change the code at all, he just added a comment that explains what the code is doing. When you run the code it will ask the user which way to sort automatically.

  36. James Says:

    Thanks a lot its really very useful

  37. James Says:

    Thanks a lot its really very useful..

  38. josh Says:

    Thank you so much. Awesome!


  39. I followed these steps and am Greatly Surprised how wonderful this worked! Thank you so much!

    A little tip for those who are running Office 2007 as I am, there is not a “Tools –> Macro –> Macros” option on the ribbon. Instead go to “View -> Macros -> View Macros”

    Thanks Again!


  40. In Office 2007, you can also do “Developer -> Visual Basic”, a lot faster shortcut!
    Thanks again!

  41. Adora Says:

    Thank you so much. That was really helpful.

  42. Gillian Says:

    You are an angel; why doesn’t Microsoft include this function? Geez, get with the times! Thanks!

  43. Susan Kearns Says:

    I thought my long standing problem had been solved, I was so happy, but all I keep getting is “Syntax error”
    when I get to the step of “sort active book and hit run. How disappointing.

    • MattyMoo Says:

      If you’re getting syntax error check the code and make sure it doesnt say ‘end sub’ at the end twice. Happened to me the first time!

      Thanks so much for the macro!

  44. Lorenzo Says:

    Awesome, Thanks so much, you’re my hero!

  45. Many Thanks Says:

    Your code is a life saver and the best I found to do the job. Thanks for sharing!

  46. Kathy Says:

    Thank you for your posting! Your instructions are well written and saved me a lot of time!

  47. Fay Says:

    Thanks very much for this. You aptly described how I felt when I started trying to sort my excel tabs today. I kept wondering if Microsoft was crazy not to have a way to do this. Your directions were clear, concise and solved my problem immediately. Our entire office thanks you!!!!

  48. Kathy Says:

    I totally agree with Fay. Even my IT guy didn’t know how to do this. I have a cheat sheet workbook that is getting quite combersome and it is nice to be able to sort it now. I keep showing people how it works by sorting it back and forth, I should charge admission! I know just enough computer skills to be dangerous and this was so simple to do. Thanks again!

  49. Temp2 Says:

    Absolutely fantastic!
    Thanks so much!

  50. Josh Says:

    Awesome, thank you so much, saved me alot of time

  51. Mark Says:

    Superb!
    Miceosoft should have just made it as a function somewhere. Excel 2010 still doesn’t seem to have it.

  52. Aaron Says:

    Hi, I was just testing out the code posted above. Does anyone know how to change the code to sort by last names on a work sheet. For example: all my tabs are named like so: ex. john doe, alvin hurd, carrie underwood, tiger woods, etc. The above posted code will sort them by the first name how can I change it to sort by last name not first without having to change my tab names?

  53. Lynda Says:

    The only Macro I’ve copied and pasted from the internet that did WHAT IT WAS SUPPOSED TO and so easy!! This is great!

    • Monica Says:

      Lynda,

      I tried sorting by number as well, and just changed my single digit sheet numbers to 01, 02, 03, etc, if the style was 2-digit, and to 001, 002, 003, etc if they were 3-digit. Works great!

  54. Janet Says:

    I ran this once – went great. Next time it said I had no digital signature or the authentication was too high. Can anyone help?

  55. Rob Says:

    This is the first time I ever posted a reply on ANY website. I had to because this tip was so helpful. Thanks a TON!

  56. bryan Says:

    Thanks a lot! This was a life saver!

  57. Chris Says:

    I don’t post much on the net, but this macros was SO helpful, I had to offer many, many thanks….THANKS!

  58. Ravi Says:

    Awesome… thank you!

  59. Kerry Says:

    Thank you! This is amazing. You’re my hero so far today. :)

  60. noone Says:

    Good effort but with codes like these you can never be a good programmer… Takes too long to execute

  61. CamPatUK Says:

    http://support.microsoft.com/kb/812386
    Which came first?

    Either way, good work son.

  62. Jan Says:

    The below code should be little bit faster as there is no value to start second For loop from “1”. Also to switch off ScreenUpdating may help when huge number of sheets.

    Sub SortSheets_Ascending()
    Dim lCount As Integer, lCount2 As Integer
    Dim lShtLast As Integer
    Dim activeSheetName As String

    Application.ScreenUpdating = False

    lShtLast = Sheets.Count
    activeSheetName = ActiveSheet.Name

    For lCount = 1 To lShtLast – 1
    For lCount2 = lCount + 1 To lShtLast
    If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
    Sheets(lCount2).Move Before:=Sheets(lCount)
    End If
    Next lCount2
    Next lCount

    Sheets(activeSheetName).Select
    Application.ScreenUpdating = True
    End Sub

  63. VWeasterly Says:

    Hi I am using Microsoft Excel 2010 and I can’t find where I can insert a macro to do this and desperately want to as I have hundreds of tabs in my spreadsheet and it grows daily. Also shoudl I use the original script or the one posted above? Many thanks in advance.

  64. Jason Says:

    THANK YOU SO MUCH. Took what I thought was never gonna happen or take forever to find a work-around and You just fixed it for me! Outstanding!

  65. Azlina Says:

    You’re a “saviour”! :) Thanks so much for your help! Totally saved me lots of time from resorting manually!

  66. moKe Says:

    Tnx worked like a charm!

  67. Amanda Says:

    Wow!! That was great! I had about 50 tabs to re-order!!

  68. Keith Says:

    Still works!!

  69. Jim Says:

    Thanks!

  70. Manas Says:

    Thanks …….

    Really good job thanks lots from my heart for your great job

  71. Grateful Says:

    Thank you Random Broad.

  72. ym Says:

    That is beautiful. Couldn’t thank you enough!

  73. SDP Says:

    AWESOME!!!! Thank you so much!

  74. jeh Says:

    cool!!! thank you very much!

  75. Chuck Says:

    Thanks a lot!! Wish it was standard in Excel… :)

  76. Egemen Says:

    Runtime error 1004
    Move method of worksheet class failed
    when I debug it highlights
    Sheets(j).Move After:=Sheets(j + 1)

    I have workbook with over 170 tabs named as 001, 002, 003, 004, 005, so on.

  77. Egemen Says:

    Every time I run the macro it moves 1 tab and gives error??? Help

  78. Dolma Hari Says:

    Hi there! This is my first visit to your blog!

    We are a team of volunteers and starting a new project in a community in the
    same niche. Your blog provided us valuable information
    to work on. You have done a marvellous job!

  79. vicki Says:

    thanks heaps… so easy!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: