Yes. In Excel for Microsoft 365 on Windows, a short VBA macro can sort a workbook’s sheet tabs alphabetically from A to Z; the macro below sorts all sheet tabs, including chart sheets, without changing the contents of the sheets. Save a copy of the workbook before running it, because VBA changes cannot be undone with Ctrl+Z.
VBA macro to sort sheet tabs A to Z
This version compares sheet names without distinguishing uppercase from lowercase. It uses a selection process: for each position, it finds the alphabetically earliest remaining sheet and moves it into place. It sorts every tab in the workbook that contains the macro.
Sub SortSheetsAlphabetically()
Dim i As Long
Dim j As Long
Dim sheetCount As Long
Dim firstSheet As Object
' Sort all sheet tabs in the workbook containing this code.
sheetCount = ThisWorkbook.Sheets.Count
For i = 1 To sheetCount - 1
Set firstSheet = ThisWorkbook.Sheets(i)
For j = i + 1 To sheetCount
If StrComp(ThisWorkbook.Sheets(j).Name, firstSheet.Name, vbTextCompare) < 0 Then
Set firstSheet = ThisWorkbook.Sheets(j)
End If
Next j
If firstSheet.Index <> i Then
firstSheet.Move Before:=ThisWorkbook.Sheets(i)
End If
Next i
End Sub
The macro changes tab order only. It does not rename sheets, sort data inside them, or change formulas and cell values. Because it uses ThisWorkbook, it targets the workbook in which the VBA code is stored. That is usually right when the macro is saved in the workbook being sorted; if the code is in Personal.xlsb or an add-in, see the workbook-targeting note below.
How to add and run the macro
- Make a backup copy of the workbook. Sheet moves are not reversible with Excel’s Undo command.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if you want the macro to remain in that file. - Open the Visual Basic Editor with Developer > Visual Basic. If the Developer tab is not visible in Windows Excel, enable it at File > Options > Customize Ribbon.
- In the editor, select the workbook project, choose Insert > Module, and paste the macro into the new module.
- Return to Excel and run it from Developer > Macros: select
SortSheetsAlphabeticallyand choose Run. The Windows shortcut for the Macro dialog is Alt+F8.
Excel may show a security warning or block a macro, depending on the file’s source and your organization’s settings. Run code only when you trust its source, and follow your organization’s macro policies. A workbook saved as .xlsx cannot retain VBA code; use .xlsm when the code needs to travel with the workbook.
What the macro sorts—and what alphabetically means
The code uses the workbook’s Sheets collection, rather than Worksheets. That distinction matters: Worksheets includes worksheet tabs, while Sheets includes worksheets and chart sheets. Consequently, the supplied code sorts both kinds of tabs together. A chart sheet is a separate sheet containing a chart, not a chart embedded in a worksheet.
Names are compared with VBA’s StrComp function and the vbTextCompare option, so the comparison ignores letter case. For example, apples and Apples are treated as equal for sorting. If two names compare equally, the macro does not intentionally impose a further case-sensitive order; their relative order may remain as it was.
Alphabetical order is a text comparison, not a human-style “natural” sort. Names such as Item 1, Item 10, and Item 2 can appear in that order because the characters are compared from left to right. If you need numeric order, use consistent zero-padded names such as Item 01, Item 02, and Item 10, or write a specialized comparison rule.
Worksheets only, or every sheet tab?
If a workbook has only ordinary worksheets, sorting Sheets and sorting Worksheets will produce the same visible result. In a workbook with chart sheets, the choice affects what gets included. The provided macro deliberately sorts every tab, which is often what people mean by “sort sheets.” It does not sort workbook names, named ranges, or tabs in another open workbook.
To sort only worksheets, change the collection references in the code from ThisWorkbook.Sheets to ThisWorkbook.Worksheets. Keep the rest of the macro unchanged. This leaves chart sheets out of the comparison, although moving worksheets can still alter their positions relative to chart tabs. If chart tabs must remain fixed at exact positions, sorting only worksheets needs additional placement logic and should be tested on a copy.
Hidden sheets are still members of the sheet collection, so the macro includes them. A hidden tab may not be visible while you watch the sort, but it can move in the tab order. The macro does not unhide anything. If hidden sheets should be excluded, the comparison needs an explicit visibility check; decide whether that means excluding all hidden sheets or only sheets hidden through Excel’s ordinary Hide command.
How the sorting logic works
The outer loop sets a position in the tab order. The inner loop looks through the remaining sheets and remembers the name that compares earliest. Once the scan is complete, the macro moves that sheet before the sheet currently at the outer-loop position. It then repeats for the next position. This approach avoids repeatedly swapping adjacent tabs and keeps the comparison logic easy to inspect.
VBA’s Move method changes the tab’s position; it does not duplicate or recreate the sheet. The code checks the sheet’s current Index before moving it, so it skips a move when the earliest remaining sheet is already in the correct position. The loops use the sheet count captured at the start because sorting changes order but does not add or remove tabs.
The macro uses the host system’s text-comparison rules through vbTextCompare. Accented letters and other language-specific characters may not sort exactly as a reader expects from a dictionary in every language or locale. If a strict custom order is important—for example, treating certain accented characters as their base letters—define and test a custom ranking rule rather than assuming a universal alphabetic sequence.
Using a macro stored outside the target workbook
ThisWorkbook always means the workbook containing the running VBA project. If you put the macro in Personal.xlsb so it is available in many files, ThisWorkbook refers to Personal.xlsb, not necessarily the workbook whose tabs you want to sort. In that case, change the target references to ActiveWorkbook if the workbook to sort is the active workbook when you run the macro.
That change makes the macro dependent on which workbook is active. Before running it, click into the intended workbook and verify its name; otherwise, you could sort the wrong file. Do not replace ThisWorkbook automatically if the macro is stored in the workbook it should affect, because the explicit reference is safer in that situation.
Common problems and safe use
- The tabs do not move: Check whether the workbook structure is protected. Excel’s workbook-structure protection prevents sheet tabs from being moved. If you have permission to change it, review the protection setting before running the macro.
- The wrong workbook changes: Check whether the code uses
ThisWorkbookorActiveWorkbook, and confirm where the VBA project is stored. The distinction is especially important for Personal.xlsb and add-ins. - The order looks unexpected: Remember that this is text sorting, so names ending in 10 can sort before names ending in 2. Uppercase and lowercase are treated alike, and language-specific characters may follow locale rules.
- A macro warning appears: Excel’s security settings can block macros, particularly in files downloaded from the internet. Do not bypass a warning for code whose source you do not trust.
- You need to undo the result: Ctrl+Z will not reverse VBA sheet moves. Restore the backup or manually move tabs back. For this reason, save a copy before every run if the original order matters.
The macro performs a sequence of moves, so a failure partway through could leave some tabs rearranged. A backup is the simplest recovery plan. For a workbook used by other people, consider documenting the intended order or adding a separate confirmation step before the macro runs.
Excel for Mac and Excel for the web
VBA macros can run in the desktop versions of Excel for Windows and Mac, although the ribbon layout and keyboard behavior can vary by release and keyboard. On a Mac, use the Developer tab to open Visual Basic and the Macros command to run the procedure; Option+F8 opens the Macro dialog on many Mac keyboards, though a function-key setting may require Fn as well. The code itself uses standard VBA sheet operations and does not rely on Windows-only APIs.
Excel for the web does not run VBA macros. You can open and edit a macro-enabled workbook in the browser, but you need desktop Excel to run this VBA procedure. If a browser-based workflow is required, use an Office Script instead, noting that Office Scripts are a separate automation technology and not VBA.
Frequently Asked Questions
Can Excel sort sheet tabs alphabetically without VBA?
Excel does not provide a built-in command to automatically alphabetize worksheet tabs. You can drag tabs manually, or use a macro such as the one above when the order needs to be applied consistently.
Does this macro sort the data inside each worksheet?
No. It rearranges sheet tabs only. To sort rows in a table, select the relevant data and use Excel’s data-sorting tools separately.
Will it sort hidden sheets and chart sheets?
Yes. The supplied code uses Sheets, which includes hidden sheet tabs and chart sheets. It does not unhide hidden sheets. Change the collection to Worksheets if chart sheets should not be part of the sort.
Can I make the macro sort Z to A?
Yes. In the comparison line, change the condition from less than zero to greater than zero. The macro will then select the alphabetically latest remaining name at each position and place it first.
Why does Sheet 10 appear before Sheet 2?
The macro compares names as text, character by character, rather than interpreting the digits as a number. Use zero-padded names such as Sheet 02 and Sheet 10 for a straightforward numeric-looking order.
Can I run the macro again safely?
Yes. Running it again will sort the current tabs into the same case-insensitive alphabetical order. It still changes the workbook, cannot be undone with Ctrl+Z, and should be preceded by a backup when preserving the original order matters.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Microsoft Excel Macro Recorder: Record and Run MacrosMicrosoft Excel’s macro recorder captures a sequence of actions in Excel and converts them into VBA code that…
- Excel VBA UserForm: How It Works and When to Use OneAn Excel VBA UserForm is a custom window for collecting information or giving users a guided way to…
- Excel Macro Blocked? How to Unblock It SafelyIf Excel says a macro is blocked, first check where the workbook came from; Microsoft 365 for Windows…
- Excel VBA: Find a Cell with Specific Text in a RangeTo find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as…
- VBA Find After: How Excel Chooses the Search Start CellIn Excel VBA, the After argument in Range.Find tells Excel which single cell to search after when beginning…
- VBA Loop Types: For, Do, and While ExplainedVBA has four commonly encountered loop constructs: For…Next, For Each…Next, Do…Loop, and While…Wend. Use For…Next when a counter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.