MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Alphabetize Worksheets


April 26, 2017 - by Bill Jelen

Alphabetize Worksheets

Pam would like to arrange her worksheet tabs alphabetically. This is not a command in Excel, but using a short macro, you can add it to your Quick Access Toolbar.

Watch Video

  • Pam wants to sort the worksheet tabs alphabetically
  • Going to use a macro in the personal macro workbook
  • Alt + T + M + S and change macro security to allow macros
  • Record a Hello World macro into the personal macro workbook
  • Type new code into Personal Macro Workbook
  • Assign that code to a QAT button

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2084 sort worksheet tabs
  • alphabetically hey welcome back to the
  • mr excel in that cast 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 all right so if
  • you've never done macros before we're in
  • do alt T for tom m for mary s for Sam
  • and make sure move from disabled of
  • macros without notification to disable
  • macros with notification alright then
  • press alt f eight 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 xls be you need to do these
  • optional steps if you already have
  • something with personal Excel speedy
  • then just hold on for a second all right
  • 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 hello world no
  • space no space and we're still where 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 right now
  • that will force you to have a personal
  • macro workbook alright if you never use
  • macros before you may not have that at
  • this point we're going to press alt f 11
  • and if you've never used macros your i
  • get this this gray screen here we're
  • going to go to view project explorer and
  • we're going to find the new personal xls
  • be if you already have a module there
  • which of course you will sure yeah open
  • modules and double-click module 1 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 spaces in all right so then you
  • would type this code that i pasted it
  • 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 this sort it was a little bit
  • disconcerting to end up on a different
  • sheet so i remembered what sheet were on
  • so we
  • I can select it right before the macros
  • done okay now here we are let's go back
  • to excel and you'll see that we 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 pad and then I want to
  • rename that currently comes in a sheet
  • one as temp sort sheet be careful this
  • should be a name that you would never
  • happen to use like don't call this
  • income statement right just use
  • something very random that way we you'll
  • run into an error if you already have a
  • sheet called tap sword sheet and then
  • what I found is that i was using this
  • worksheet stuck out over and over and
  • over again so i'm just going to put it
  • in a variable as easier to say WSC then
  • worksheets count later on so we have a
  • hundred and fifty worksheets and we're
  • at list those worksheets alright so
  • you're on the first time through the
  • loop where I go from 1 250 on the first
  • time through the loop to our brand-new
  • shoe that we added the taps or sheet on
  • Row 1 column 1 we're going to put a
  • value that's equal to the first
  • worksheet all right and what that should
  • be is well taps or sheet 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 pursuing I start to get
  • those tabs that have the account numbers
  • right and I'm going to let this run I
  • don't wanna have to press f8 300 times
  • so I'm going to click here and go to
  • debug run the cursor which is ctrl f 8
  • 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 mr excel 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 as a sort and I'm
  • using the code from Excel 2003 I like
  • this code easier to remember I'm gonna
  • start an a.1 resize it to be 150 cells
  • where I sort that the key is going to be
  • a one month's the only thing it could be
  • orders going to be a sentient and make
  • sure specify that there is a header all
  • right we don't want our temps
  • or cheat to sort down into the data all
  • right set right there band 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 a 120 the sheet
  • to move is a 120 why the quote quote in
  • case you have a sheet name like we had
  • earlier this week like I she named 123
  • that would confuse Excel and then where
  • we're going to move it to we're going to
  • move to not Row 2 but after the first
  • sheet so move after becomes one so we're
  • gonna take our she called a 120 and move
  • it after the first sheet like that f8
  • and there it worked run those three
  • lines one more time a 124 comes to the
  • right spot at this point now we know
  • it's working we can do ctrl f 8 which
  • does all of the moving 150 moons just
  • stamp in there and now i'm going to
  • delete the original sheet that is our
  • delete the temps or cheat 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
  • things we want to do alt f 11 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 xls
  • be and you'll completely forget and not
  • safe 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 sort sheets add that to
  • the quick access toolbar I don't like
  • the flow chart so we'll look for someone
  • says a to z or you know so i let's use
  • this one and put a little tab there of
  • sort sheets like that click OK click ok
  • all right now here's the test I'll
  • insert a new 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 be like that click sort sheets and
  • pan they're back in here in the right
  • sequence how cool is that all right so
  • today's episode Pam lift to sort the
  • worksheet tabs alphabetically Wow we
  • used a macro first change the macro
  • security and record a hello Mac hello
  • world macro into the personal matter
  • workbook and then type the code in the
  • personal macro work what can assign that
  • to a quick access toolbar button I think
  • Pam for that question I want at you for
  • stopping by see you next time for
  • another netcast from MrExcel

Download File

Download the sample file here: Podcast2084.xlsm

Title Photo: congerdesign / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.