Alphabetize Worksheets

Learn Excel from MrExcel Podcast, Episode 2084: Sort Worksheet Tabs Alphabetically

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Today's question from Pam: Is there a way to alphabetize the worksheet tabs?  Well, there's no built-in way to do that but we could do it with a macro.  And now, this is a Macro that really should be in the personal macro workbook, alright?  So, if you've never done macros before, we're going to do Alt+T for Tom and for Mary, S for Sam.  And make sure, you move from Disable all macros without notification to Disable macros with notification.

Alright then, press Alt+F8 and take a look and see if you have any macros, make sure to choose Macros in: All Open Workbooks.  If you don't have anything that starts as PERSONAL.XLSB, you need to do these optional steps.  If you already have something with PERSONAL.XLSB then just hold on for a second, alright? 

So, we're going to go out to the View tab and we're going to Record a New Macro.  It's going to be called HelloWorld.  No space, no space.  And we're going to store the macro in the Personal Macro Workbook, click OK.  And just type somewhere Hello World, like that, and then come down here next to Ready and click Stop.  Alright, now that will force you to have a Personal Macro Workbook.  Alright, if you’ve never use macros before, you may not have that. 

At this point, we're going to press Alt+F11 and if you've never used macros, you’re going to get this gray screen here.  We're going to go to View, Project Explorer and we're going to find the new PERSONAL.XLSB.  If you already have a Module there which of course you will, sure yeah, open modules and double-click Module 1.  Now, I already have a couple of macros in my Personal Macro Workbook but I'm going to come right down here to the last row, put a couple of spaces in.

Alright, so then you would type this code that I pasted in.  And we're going to talk about this macro as I run it.  So, I'm going to run it one line at a time by pressing the F8 key.  And one thing I learned here is after finishing the sort, it was a little bit disconcerting to end up on a different sheet, so I remember to what sheet we’re on, so later on, I can select it right before the macro is done.

Okay now, here we are.  Let's go back to Excel and you'll see that the first sheet there is called Title.  We're currently on a sheet called Question.  I want to add a new sheet before Title, so we do Worksheets.Add and then I want to rename that.  Currently comes in a Sheet1 as TempSortSheet.  Be careful, this should be a name that you would never happen to use.  Like don't call this Income Statement, alright?  Just use something very random so when we- you'll run into an error if you already have a sheet called TempSortSheet.  And then what I found is that I was using this Worksheet.Count over and over and over again, so I'm just going to put it in a variable as easier to say WSC than Worksheets.Count later on.  So we have 150 worksheets and we have list of those worksheets.

Alright, so you're on the first time through the loop where I go from 1 to 250.  On the first time through the loop to our brand new sheet that we added, the TempSortSheet, on Row 1 Column 1, we're going to put a Value that's = to the first Worksheet.  Alright, and what that should be is well, yup, TempSortSheet right there.  But I'm going to let this run a couple more times.  I'll press F8 and you'll see that we're getting all of the sheet tabs in the order that they're appearing in the workbook, and presume I start to get those tabs that have the account numbers, alright.  And I'm going to let this run, I don't want to have to press F8 300 times.  So, I'm going to click here and go to Debug, Run To Cursor which is Ctrl+F8 which will finish that out.  And see we have a list of all 150 sheet tabs.  That's awesome. 

And now here's where we take advantage of the power of Excel.  There's a lot of postings online including at the MrExcel message board, where they actually try and sort.  They write their own sort to move the sheets around and we're going to take advantage of Excel here because Excel has a sort and I'm using the code from Excel 2003.  I like this code, easier to remember.  I'm going to start in A1, Resize it to be 150 cells where I Sort that.  The key is going to be A1.  Well, that’s the only thing it could be.  Order is going to be Ascending, and make sure to specify that there is a Header, alright?  We don't want our TempSortSheet to sort down into the data.  Alright, so right there.  BAM!  That does the sort and we don't need to write around bubble sort code.  And then we're going to go from Row 2 which is A120 , the sheet to move is A120.  Why the “ ”  ?  Ahh, in case you have a sheet name, like we had it earlier this week, like a sheet named 123 that would confuse Excel.  And then, where we are going to move it to?   We're going to move to not Row 2 but after the first sheet.  So MoveAfter becomes 1.  So, we're going to take our sheet called A120 and move it after the first sheet like that, F8, and there it worked.  Run those three lines one more time.  A124 comes to the right spot. 

At this point, now we know it's working, we can do Ctrl+F8 which does all of the moving - 150 moves just happened there.  And now, I'm going to delete the original sheet that is our deleted TempSortSheet.  But before I delete it, I don't want them to tell whoever's running the code that we're about to delete the sheet.  So, I just delete it and then select the original sheet and display message that the workbooks have been sorted like that. 

Okay, so now that we have the macro, we know the macro is working and it's in the personal macro workbook, a couple of things we want to do: Alt+F11 come back here and make sure that you're in the personal macro workbook, click there and click Save.  If you don't save it now, later on when you're closing Excel it's going to flash up this message about ‘Do you want to save changes to PERSONAL.XLSB?’ and you'll completely forget and not save the changes, right?  So I always make sure to save the changes and then Alt+Tab where I Customize the Quick Access Toolbar, choose from Macros, find the one that's called SortSheets, add that to the Quick Access Toolbar.  I don't like the flowchart so we'll look for something that says A to Z or you know, so let's use this one and put a little tab there of Sort Sheets like that, click OK, click OK. 

Alright, now here's the test.  I'll insert a new workbook.  I'll make some copies of the sheets and we'll put them in kind of a weird sequence, Z and then Y and then M and then B, like that.  Click Sort Sheets and BAM!  They're back in here in the right sequence.  How cool is that.

Alright, so today's episode, Pam wants to sort the worksheet tabs alphabetically.  Wow!  We used the macro.  First, change the macro security and record Hello World macro into the personal macro workbook and then type the code in the Personal Macro Workbook and assign that to a Quick Access Toolbar button.

I want to thank Pam for that question.  I want to thank you for stopping by.  We’ll see you next time for another netcast from MrExcel.