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