Yes. In Excel for Microsoft 365 on Windows, VBA can sort a workbook’s worksheet tabs alphabetically; the macro below compares worksheet names without regard to uppercase or lowercase and moves the tabs into A-to-Z order. Save a copy of the workbook before running it, because VBA macros cannot be undone with Ctrl+Z.
What the macro sorts
The code sorts worksheets in the workbook that contains the macro. It includes visible, hidden, and very hidden worksheets, because all are members of the workbook’s Worksheets collection. It does not sort chart sheets, which are separate sheet objects, and it does not sort worksheets in other open workbooks.
Alphabetical here means a text comparison, not a numerical or “natural” sort. For example, names such as “Report 1,” “Report 10,” and “Report 2” sort in that text order: “Report 1,” “Report 10,” then “Report 2.” The comparison ignores case, so “budget” and “Budget” are treated alike. Accented letters and other characters follow the text-comparison rules of the computer’s locale, so the exact order of unusual characters can vary.
Excel does not provide a standard ribbon command that automatically alphabetizes worksheet tabs. You can drag tabs manually, but a macro is more practical when a workbook has many sheets or needs to be sorted repeatedly.
VBA code to sort worksheets A to Z
This insertion-sort macro moves each worksheet to its correct position among the worksheets already considered. It uses StrComp with vbTextCompare for a case-insensitive comparison. The workbook’s sheet order changes, but the worksheets’ names and contents do not.
Sub SortWorksheetsAlphabetically()
Dim wb As Workbook
Dim sheetToMove As Worksheet
Dim i As Long
Dim j As Long
Dim screenUpdatingWasOn As Boolean
' ThisWorkbook means the workbook containing this macro.
Set wb = ThisWorkbook
' Excel cannot move sheets while workbook structure is protected.
If wb.ProtectStructure Then
MsgBox "Unprotect the workbook structure before sorting sheets.", vbExclamation
Exit Sub
End If
screenUpdatingWasOn = Application.ScreenUpdating
On Error GoTo HandleError
Application.ScreenUpdating = False
For i = 2 To wb.Worksheets.Count
Set sheetToMove = wb.Worksheets(i)
j = i - 1
Do While j >= 1
If StrComp(wb.Worksheets(j).Name, sheetToMove.Name, vbTextCompare) <= 0 Then
Exit Do
End If
sheetToMove.Move Before:=wb.Worksheets(j)
j = j - 1
Loop
Next i
Application.ScreenUpdating = screenUpdatingWasOn
MsgBox "Worksheets are sorted alphabetically.", vbInformation
Exit Sub
HandleError:
Application.ScreenUpdating = screenUpdatingWasOn
MsgBox "Sorting stopped: " & Err.Description, vbExclamation
End Sub
The procedure checks for workbook-structure protection before it starts, then restores the prior screen-updating setting when it finishes or encounters an error. If an error message appears, read it rather than assuming the sort completed. The code does not change calculation settings or disable Excel events.
How to add and run the macro
- Make a separate copy of the workbook. This is important because Excel’s Undo command does not reverse changes made by a macro.
- In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Tools > Macro > Visual Basic Editor; depending on the keyboard, a function-key combination may also be needed.
- In the Visual Basic Editor, choose Insert > Module, then paste the complete code into the module.
- Save the workbook in a macro-capable format such as Excel Macro-Enabled Workbook (*.xlsm) if it is not already saved that way. The standard .xlsx format cannot store VBA code.
- Return to Excel. On Windows, press Alt+F8, select
SortWorksheetsAlphabetically, and choose Run. On Mac, open Tools > Macro > Macros, select the procedure, and run it.
If the code is stored in a different workbook—for example, in Personal.xlsb—ThisWorkbook refers to the file containing the code, not necessarily the file whose tabs you are viewing. To target the currently active workbook, you can change Set wb = ThisWorkbook to Set wb = ActiveWorkbook. Do that only when you are sure the intended workbook is active; otherwise, the macro could sort the wrong file.
What happens during the sort
The macro starts with the second worksheet and compares it with the worksheet immediately before it. If the earlier name belongs later alphabetically, the macro moves the current worksheet in front of it. It continues comparing backward until the current tab is in the right position, then proceeds to the next worksheet. Repeating that process produces ascending order.
For example, if the worksheet tabs are “Summary,” “April,” and “Archive,” the macro first considers “April” and moves it before “Summary.” It then considers “Archive” and places it between “April” and “Summary.” The final order is “April,” “Archive,” “Summary.” A workbook with zero or one worksheet needs no movement; the loop simply has no sorting work to do.
The macro uses the name of each worksheet at run time. Renaming a worksheet and running the macro again therefore sorts using the new name. It does not rename sheets to add numbers, change tab colors, or alter the active worksheet as a separate goal.
Important workbook effects to check
Changing sheet order can affect formulas that refer to a three-dimensional range of sheets. For instance, a formula using a range from a sheet named “Jan” through a sheet named “Dec” can include worksheets positioned between those endpoints. Alphabetizing tabs may change which worksheets fall inside that range, and therefore may change the formula’s result. Check formulas using sheet-range references before sorting, especially in financial or reporting workbooks.
Ordinary formulas that refer to a worksheet by name generally continue to refer to that named worksheet after it moves. However, do not assume that every workbook is independent of tab order: review 3-D references, macros that use worksheet indexes, and any process that expects a particular tab to be first or next to another tab.
Chart sheets are not included in the comparisons. Since a chart sheet can sit among the workbook’s tabs, moving worksheets may change its position relative to worksheets even though the macro never compares chart-sheet names. If you need chart sheets sorted too, that requires a different routine that works with the broader Sheets collection and defines how chart sheets should be ordered alongside worksheets.
Common problems and how to fix them
- The macro cannot move the tabs. The workbook structure may be protected. In Excel, check Review > Protect Workbook. If structure protection is on, an authorized user must unprotect it before sheets can be moved. A worksheet’s cell protection is a different setting and is not the same as workbook-structure protection.
- The macro is missing or disabled. Confirm that the code is in a standard module and that the file is saved as .xlsm or another macro-capable format. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Open the file in desktop Excel to run this procedure.
- The wrong workbook gets sorted. This can happen if the code uses
ThisWorkbookwhile it is stored in a different file. Use the intended workbook as the code container, or carefully switch toActiveWorkbookas described above. - The order looks unexpected. Text sorting puts “Page 10” before “Page 2.” If numeric order is important, use consistent names such as “Page 02” and “Page 10,” or write a custom comparison that separates the numeric part from the text.
- Excel blocks the macro for security. Run macros only from files and sources you trust. Do not enable all macros globally just to run a sorting routine. If the workbook is from a trusted source, follow your organization’s macro-security policy.
Options for adapting the approach
For a one-time job with only a few worksheets, dragging the tabs may be quicker than installing a macro. For repeat work, the code can be saved in the workbook and run again after new tabs are added. If the workbook is distributed to other people, consider whether they need the macro and whether their Excel security settings permit it.
This version deliberately uses a case-insensitive, locale-aware text comparison. A case-sensitive order would require changing the comparison mode to vbBinaryCompare, but that makes uppercase and lowercase characters sort separately according to their character values. A natural sort that recognizes “2” as less than “10” needs additional logic; changing the existing comparison flag alone will not provide that behavior.
Do not remove the backup precaution just because the procedure is short. A macro can make changes that are not recorded in Excel’s normal Undo history, and sorting may matter to formulas or other automation even when every tab name remains unchanged. A copy gives you a straightforward way to restore the original order and verify any affected results.
Frequently Asked Questions
Does this macro sort hidden worksheets too?
Yes. It uses the Worksheets collection, which includes hidden and very hidden worksheets. Their visibility settings remain as they were; the macro changes their position in the workbook’s worksheet order.
Will it sort chart sheets alphabetically?
No. Chart sheets are not worksheets, so this code does not compare their names or arrange them with the worksheet names. Use a routine designed for all sheet types if chart sheets must be included.
Can I run this VBA macro in Excel for the web?
No. Excel for the web can retain a workbook that contains VBA, but VBA macros must be run in desktop Excel. Open the workbook in the desktop application to add or execute the code.
Can I undo the sort with Ctrl+Z?
No. Excel’s Undo command does not undo VBA macro actions. Save a separate copy before running the macro; you can then return to that copy if you need the original tab order.
Does the macro sort names like Sheet 1, Sheet 2, and Sheet 10 numerically?
No. It compares the names as text, so “Sheet 10” comes before “Sheet 2.” Zero-padding the numbers in the names, such as “Sheet 02,” is a simple way to make text order match the intended numeric order.
Related
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Filter a PivotTable by Field or ValueYes. In Excel VBA, filter a PivotTable by setting a report-filter field’s CurrentPage, applying a label or value…
- VBA MsgBox Buttons: Options, Examples, and Return ValuesIn VBA, the MsgBox function’s buttons argument controls which buttons and icon appear, which button is selected by…
- Excel VBA Pivot Table Source Types ExplainedIn Excel VBA, the main PivotTable source types are xlDatabase for a worksheet range or table, xlExternal for…
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to…
- Excel Macro Refresh All: How to Update Workbook DataIn Excel for Microsoft 365 on Windows, use the VBA method ThisWorkbook.RefreshAll to request a refresh of the…
- VBA Delete an Entire Row Based on Cell Value in ExcelTo delete entire Excel rows when a cell matches a value, loop through the worksheet from the bottom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.