How to sort worksheet tabs in alphabetical order in excel

Lori Kaufman
Lori Kaufman
Writer

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

If you have a large number of worksheets in your Excel workbook, it may be hard to find a specific worksheet. Sorting your worksheet tabs alphabetically would make it easier to find what your looking for.

In addition to organizing your worksheet tabs by applying colors to them, you can also sort them alphabetically or alphanumerically, as long as you’ve applied custom names to your worksheets. Unfortunately, sorting worksheet tabs alphabetically is not built in to Excel, but you can add a macro to your workbook that will allow you to sort your tabs in ascending or descending order. We’ll show you how to add a macro available on Microsoft’s support site to your Excel workbook that will sort your worksheet tabs.

To begin, press Alt+F11 to open the Microsoft Visual Basic for Applications (VBA) editor. Then, go to Insert > Module.

Copy and paste the following macro from Microsoft into the module window that displays.

The VBA editor automatically names each module with a number on the end, such as Module1, Module2, etc. You can simply accept the default name of the module. However, if you plan to add other macros to your workbook, it’s a good idea to rename each module so you know what they are. We’ll rename our module to show you how.

To rename the module, select the text in the Name box for the module under Properties in the left pane.

Type a name for the module in the Name box and press Enter. Note that the module name cannot contain spaces.

The name of the module changes in the Modules list under Project in the left pane.

Close the VBA editor by going to File > Close and Return to Microsoft Excel.

Now, we’re going to run the macro to sort our tabs. Press Alt+F8 to access the list of macros on the Macro dialog box. Select the macro in the list (in our case there is only one macro), and click “Run”.

The following dialog box displays, allowing you to choose whether you want to sort your worksheets in ascending or descending order. We want to sort them in ascending order, so we click “Yes”.

The worksheet tabs are now arranged in alphabetical order.

The macro you added is part of your workbook now, but when you save it, you’ll probably see the following dialog box. That’s because you saved your workbook as an .xlsx file, which is a normal Excel workbook format that does not include macros. To include macros in your workbook, and be able to run them, you must save your workbook as a macro-enabled workbook, or an .xlsm file. To do this, click “No” on this dialog box.

The Save As dialog box displays. Navigate to where you want to save the macro-enabled workbook, if you’re not already in that folder. Select “Excel Macro-Enabled Workbook (*.xlsm)” from the “Save as type” drop-down list.

If you don’t save the workbook as a macro-enabled workbook (.xlsm file), the macro you added will be deleted. You might want to delete the .xlsx version of your workbook so you don’t forget to use the .xlsm version of your workbook if you want to add more worksheet tabs and sort them again using the macro. You can always save the workbook as an .xlsx file again if you don’t want to use macros anymore.

• › The Best Gaming Keyboards of 2021: Be on Top of Your Game
• › Why Sublime Text Is Great For Writers, Not Just Programmers
• › What Is a ULED TV, and How Is It Different?
• › Why Professionals Will Actually Want a 2021 MacBook Pro
• › How to Add Images to Questions in Google Forms

Lori Kaufman
Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business.
Read Full Bio »

Microsoft Excel provides a number of quick and easy ways to arrange columns or rows in alphabetical order. But there is only one method to rearrange worksheets in Excel – drag them to the desired position on the sheet tab bar. When it comes to alphabetizing tabs in a really large workbook, this may be a long and erroneous way. Looking for a time-saving alternative? There exist only two: VBA code or third-party tools.

How to alphabetize tabs in Excel with VBA

Below you will find three VBA code examples to sort Excel sheets ascending, descending, and in either direction based on the user’s choice.

Implying that you have some experience with VBA, we will only outline the basic steps to add a macro to your worksheet:

1. In your Excel workbook, press Alt + F11 to open the Visual Basic Editor.
2. On the left pane, right-click ThisWorkbook, and then click Insert > Module.
3. Paste the VBA code in the Code window.
4. Press F5 to run the macro.

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Alternatively, you can download our sample Alphabetize Excel Tabs workbook, enable content if prompted, and run the desired macro directly from there. The workbook contains the following macros:

• TabsAscending – sort sheets alphabetically from A to Z.
• TabsDescending – arrange sheets in the reverse order, from Z to A.
• AlphabetizeTabs – sort sheet tabs in both directions, ascending or descending.

With the sample workbook downloaded and open in your Excel, open your own workbook where you want to alphabetize tabs, press Alt + F8 , select the desired macro, and click Run.

Sort Excel tabs alphabetically from A to Z

This little macro arranges the sheets in the current workbook in ascending alphanumeric order, first worksheets whose names start with numbers, then sheets from A to Z.

Arrange Excel tabs from Z to A

If you want to sort your sheets in descending alphanumeric order (Z to A, then sheets with numeric names), then use the following code:

Alphabetize tabs ascending or descending

This macro lets your users decide how to sort worksheets in a given workbook, alphabetically from A to Z or in the reverse order.

Since the standard dialog box (MsgBox) in Excel VBA only allows choosing from a handful of predefined buttons, we will create our own form (UserForm) with three custom buttons: A to Z, Z to A, and Cancel.

For this, open the Visual Basic Editor, right-click ThisWorkbook, and click Insert > UserForm. Name your form SortOrderFrom, and add 4 controls to it: a label and three buttons:

Next, press F7 (or double-click the form) to open the Code window and paste the below code there. The code intercepts button clicks and assigns a unique tag to each button:

Depending on whether the user clicks the A to Z or Z to A button on your form, sort tabs in ascending alphabetical order (selected by default) or descending alphabetical order; or close the form and do nothing in case of Cancel. This is done with the following VBA code, which you insert in the usual way via Insert > Module.

If you are not very comfortable with VBA yet, you can simply download our Sample Workbook to Alphabetize Tabs, open it in your Excel alongside your own file where you want to sort tabs, and run the AlphabetizeTabs macro from your workbook:

Choose the preferred sort order, say, A to Z, and observe the results:

How to sort Excel tabs alphabetically with Ultimate Suite

The users of our Ultimate Suite for Excel don’t have to fiddle around with VBA – they have a multi-functional Workbook Manager at their disposal:

With this tool added to your Excel ribbon, alphabetizing tabs is done with a single button click, exactly as it should be!

If you are curious to explore this and 60+ more professional tools for Excel, a trial version of our Ultimate Suite is available for download here.

Unfortunately, sorting worksheet tabs alphabetically is not built in to excel, but you can add a macro to your workbook that will allow you to sort your tabs in ascending or descending order. we’ll show you how to add a macro available on microsoft’s support site to your excel workbook that will sort your worksheet tabs. 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. This macro lets your users decide how to sort worksheets in a given workbook, alphabetically from a to z or in the reverse order. since the standard dialog box (msgbox) in excel vba only allows choosing from a handful of predefined buttons, we will create our own form (userform) with three custom buttons: a to z , z to a , and cancel . If you have a large number of worksheets in your excel workbook, it may be hard to find a specific worksheet. sorting your worksheet tabs alphabetically woul. How to alphabetically sort excel tabs. excel details: excel details: easiest way to order excel tabs in alphabetical order a .excel details: next navigate to the data tab on the excel ribbon and click the a z icon for ascending order sort or the z a icon for descending sort. 2 then the visual basic editor window will appear. 1 open your excel.

How To Sort Excel 2010 By Alphabetical Order In Excel

The fastest way to sort alphabetically in excel is this: select any cell in the column you want to sort. on the data tab, in the sort and filter group, click either a z to sort ascending or z a to sort descending. done! the same buttons can also be accessed from home tab > editing group > sort and filter:. Sort worksheets in alphabetical order excel how. excel details: #1 open your excel workbook and then click on “visual basic” command under developer tab, or just press “alt f11” shortcut. #2 then the “visual basic editor” window will appear. #3 click “insert” >”module” to create a new module #4 paste the below vba code into the code window.then clicking “save” button. How to alphabetically sort excel tabs. excel details: excel details: easiest way to order excel tabs in alphabetical order a .excel details: next navigate to the data tab on the excel ribbon and click the a z icon for ascending order sort or the z a icon for descending sort. 2 then the visual basic editor window will appear. 1 open your excel workbook and then click on visual basic command.

How To Sort Worksheet Tabs In Alphabetical Order In Excel

How To Sort Worksheet Tabs In Alphabetical Order In Excel

How To Arrange Worksheet Tabs In Excel In An Alphabetical Order || Sort Excel Worksheets In Order

Are you looking for a shortcut to quickly rearrange your worksheets in alphabetical order? Instead of dragging and manually arranging your worksheets, you can use VB for Microsoft® Excel® to quickly rearrange them into alphabetical order.

Excel currently has no built-in function to rearrange your worksheets. Therefore, if you have a workbook with many worksheets, the task of rearranging them may become long and drawn out, but luckily there is a way for this to be simplified. Follow our step by step instructions on how that can be done.

Applies to: Microsoft ® Excel ® 2013 and 2016.

1. Press ALT + F11 to open VB for Excel.
2. From the Menu bar, Select Insert–Module.
3. On the right hand side, copy & paste the Visual Basic coding below:

Sub SortWorksheets()

‘ sort worksheets in a workbook in ascending order

Dim sCount As Integer, i As Integer, j As Integer

Application.ScreenUpdating = False

sCount = Worksheets.Count

If sCount = 1 Then Exit Sub

For i = 1 To sCount – 1

For j = i + 1 To sCount

If Worksheets(j).Name

Worksheets(j).Move Before:=Worksheets(i)

End If

Next j

Next i

End Sub

1. From the Menu bar, select File, Close and Return to MS Excel.
2. Press ALT + F8 to get a list of macros.
3. Select Sortsheets.
4. Select Run.

N.B:

• When saving the workbook, Save As Macro Enabled Workbook.
• Ensure that you set the appropriate macro security level:
  • Select the File Tab
  • Select the Excel Options button
  • On the left side of the dialogue box, select Trust Centre
  • On the right side, select the Trust Center Settings button
  • On the left side, select Macro settings
  • Select Disable all macros with notification
  • Select OK

  The worksheets will quickly be sorted in ascending order, thus saving you time.

  Normally there is no quick way for you to sort or arrange the order of sheets in Excel, either in alphabetical or numeric order. You may need to sort sheet tabs order manually one by one when you want to sort the sheets order. But with Kutools for Excel’s Sort Sheets utility, you can quickly:

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

  Click Kutools Plus >> Worksheet >> Sort Sheets. See screenshot:

  Sort sheets / worksheets alphabetically

  With this utility, you can sort the sheets / worksheets in alphabetical order with one click.

  1. Apply this utility by clicking Kutools Plus > Worksheet > Sort Sheets.

  2. In the Sort Sheets dialog box, click Alpha Sort button, and you can preview the result in the New sheet tabs order list box. See screenshot:

  If you need to sort the sheet tabs in alphanumeric order, this utility also can help you.

  1. Open the workbook you want to sort sheets / worksheets alphanumerically.

  2. Go to the Sort Sheets dialog box, and click Alpha Numeric Sort button and you can preview the result in the New sheet tabs order list box. See screenshot:

  Supposing you have a workbook with colored worksheet tabs, in order to looks neatly, you want to sort the sheets / worksheets by tab color. With the Color Sort option, you can do it as follows:

  1. Apply this utility by clicking Kutools Plus > Worksheet > Sort Sheets.

  2. In the Sort Sheets dialog box, click Color Sort button, and you can preview the result in the New sheet tabs order list box. See screenshot:

  Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

  Kutools for Excel

  The functionality described above is just one of 300 powerful functions of Kutools for Excel.

  Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

  If you work with a lot of worksheets in Excel, you would know the management of it can become an issue.

  Once you have more than a couple of worksheets, you need to manually arrange these.

  How easy would it be had there been a way to quickly sort the worksheets in Excel.

  While there is no inbuilt feature way to do this, it can be done (easily) using VBA.

  In this tutorial, I will give you the code and the exact steps you need to follow to sort worksheets in Excel.

  You can tweak the code to sort the worksheets in an ascending or descending order.

  VBA code to Sort Worksheets in Excel

  Below is the code that will sort the worksheets in an alphabetical order as soon as you run it.

  The above is a simple code that uses to For Next loops to analyze each worksheet against all the worksheets.

  It compares the name of a worksheet against all the worksheets and moves it based on its name in the alphabetical order.

  It then moves on to the next worksheet and then checks it against all the worksheets.

  This process is repeated for all the worksheets and the final result is an order of worksheet sorted in an alphabetical order.

  A few important things to know about this code:

  is used to make sure that the lowercase and uppercase are not treated differently.
• The value of Application.ScreenUpdating is set to False at the beginning of the code and changed to True at the end of the code. This ensures that while the code is running, you don’t see it happening on the screen. This also helps speed up the code execution.

If you want to sort worksheets in a descending order, you only need to change the (greater than) sign.

The below code would sort the worksheets in descending order:

You can also give the user the option to choose whether he/she wants to sort in ascending/descending order.

The below code would show a message box and the user can select the order to sort.

The above code when executed shows a message as shown below. It sorts based on the selection (Yes for Ascending and No for Descending).

In case you click Cancel, the code stops and nothing happens.

Note: The sorting cannot be undone. In case you want to keep the original order as well, make a copy of the workbook.

Where to Put the VBA Code

Excel has a VBA backend called the VBA editor.

You need to copy and paste the VBA code into the VB Editor module code window.

Here are the steps to do this:

1. Click the ‘Developer’ tab. (Can’t see the developer tab? Click here to learn how to get it).
2. Click on Visual Basic option. This will open the VB editor in the backend.
3. In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. (If you don’t see the Project Explorer go to the ‘View’ tab and click on ‘Project Explorer’.)
4. Go to Insert and click on Module. This will insert a module object for your workbook.
5. Copy and paste the code in the module window.

How to Run the VBA Code

In Excel, there are various ways to run the VBA code.

You can run the code right from the Visual Basic Editor (also called the VB Editor).

You can insert a button or a shape in the worksheet and assign the macro to it. When you click on the button, it will run the macro instantly.

You can also add the macro to the Quick Access Toolbar (QAT). Now whenever you have to sort the worksheet tabs, you can just click on the macro code icon in the QAT.

You May Also Like the Following Excel/VBA Tutorials:

When working with Excel, the worksheets order matters. The tabs should reflect the flow which a user can understand. Since there is no native feature to do it, using VBA to short sheets alphabetically can be a useful time-saver.

• Sorting can be in standard or reverse order
• In Excel, sheets and worksheets are different. Worksheets are only those with cells, while sheets can include worksheet and charts sheets
• Worksheet names containing numbers are not sorted as numbers but as text

In this post, I will give you the necessary code to deal with all these situations.

VBA to sort worksheets alphabetically

Enter the following code into a standard code module:

Adapting the code

• Reverse the order
• Apply to other workbooks
• Sort numbers as numbers
• Sort including chart sheets

Let’s take a look at these options in turn.

Reverse the order

To reverse the order, we change the direction logic sign.

Change this line of code:

Change the workbook

The VBA code can also be changed to work on any open workbooks.

Active workbook:

This is the code currently used in the example. If you want to change the code, switch this line to one of the lines below.

Named workbook:

Remember to change WorkbookName.xlsx for the name of your workbook.

Nth workbook opened

The number refers to workbooks in the order opened, where 1 is the first workbook, 2 is the second workbook, etc.

Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.

• 100 example codes to practice reading and writing macros that will embed the language into your thinking.
• An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
• Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
• Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Sort numbers as numbers

All worksheet names are text strings. Therefore, if we had sheets named “1”, “2”, and “12”, their sorted order would be “1”, “12” and “2”. But we can change the basic code to sort numbers as numbers.

All changes from the original code have been highlighted in bold.

Sort sheets instead of worksheets

As stated above, chart sheets are included in the sheets collection, but not the worksheets collection. Therefore to include chart sheets into the scope of the code, we can use the following code as a base.

The changes from the original code have been highlighted in bold.

The same amendments to reverse sort, sort numbers, and sort other workbooks can also be made to this code.

Related pages which may be useful to you are:
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
2. Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:

I’ve seen multiple threads in the archive about how to sort tabs alphabetically; is there any way to arrange tabs strictly by number? For example:

would be arranged as:

I suspect this may be more difficult than it seems. I know that I could just rename lower numbers with preceeding zeros (e.g., 01 instead of 1), but for large numbers of tabs, this would be cumbersome.

Thanks in advance for any help!

Excel Facts

Tazguy37

MrExcel MVP

• Sep 7, 2005

• #2

I took the code out of this thread, and replaced in the BubbleSort() sub:

so it would just look at the numerical value. You could modify the code here to look at only the selected sheets, so some could be sorted by number, and others by text, as well.

saetveit

New Member

• Sep 7, 2005

• #3

That worked perfectly. Now I’ve noticed another slight issue with the code for sorting tabs. I tried this with the code you referenced, as well as the following code, which is shorter and seems to do the same thing:

Sub AlphaTabs()
‘Alphabetizes Excel Worksheet Tabs
‘Unknown MVP site, via JeffCoach
Application.ScreenUpdating = False
Count = Sheets.Count
For i = 1 To Count – 1
For j = i + 1 To Count
If Sheets(j).Name Sheets(j).Move Before:=Sheets(i)
End If
Next
Next
End Sub

(I modified the code to add Val(. ), as you suggested.)

For some reason, when the macro is run, it copies one of the tabs twice. The next time the macro is run, it copies the next highest tab twice, and so on. I’m sure with some more knowledge about scripts, I could figure out why. but I found this code posted by Hotpepper, which does not have this problem:
____________________

Keeping your data and calculations on separate tabs is easy for an organized structure, but too many tabs in a workbook can become overwhelming quickly. In this article, we’re going to show you how to alphabetize Excel worksheets in ascending or descending alphabetical order using VBA.

How to alphabetize Excel tabs

The idea behind sorting tabs is to organize your workbook and make things easier to find. We can compare names with a greater than (>) character just like comparing numbers. Since every character has a numerical ANSI code, VBA can handle this type of a comparison. You can check the links below to see the ANSI codes of alphanumerical characters.

We begin by converting all characters to upper case to eliminate the differences between the ANSI codes of upper case and lower case characters.

Next, we need to compare and to move the sheet if the condition is met.

Our codes use two For Loops to check the names one by one. The nested loop allows comparing a sheet with the name of the other sheets (j loop), for the number of existing sheets (I loop).

To run this code, you need to add a module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.