Excel VBA: sort worksheet tabs alphabetically

Key insights

• The macro reads worksheet names from the active workbook and stores them in an array.
• Application.WorksheetFunction.Sort sorts the names; the by_col argument must be True for this array.
• The macro moves each sorted worksheet into its corresponding position in the workbook.
• The demonstration recommends storing the code in a personal macro workbook or another file if you want to keep the target workbook macro-free.

Excel VBA can alphabetize worksheet tabs by sorting their names and moving each sheet to its new position, avoiding manual tab dragging. This approach is useful for Excel users who maintain workbooks with many sheets, such as one tab per city. Chandoo demonstrates the technique in a YouTube video using a workbook with a large set of city-based sheets.

What it does

The macro separates the task into three parts: collect the worksheet names, sort those names, and move the worksheets to match the sorted order. That matters because sorting a list of names does not, by itself, change the order of the tabs in the workbook. The final movement step applies the sorted list to the workbook structure.

The example uses VBA, Excel’s Visual Basic for Applications environment, and the SORT worksheet function called from VBA. It sets the target workbook to ActiveWorkbook, so the workbook active when the macro runs is the one whose sheets are processed. The demonstration first checks that workbook’s sheet names and then uses the result to arrange the tabs alphabetically.

For people who routinely handle large Excel workbooks, this offers an alternative to rearranging every tab by hand. It is a specific workbook-organization task, not a general-purpose sort of worksheet data. The demonstrated result puts the sheet named Coromandel first and Wigram among the final tabs.

Who it is for and what you need

This is aimed at Excel users who can access the Visual Basic Editor and are comfortable running a VBA procedure, including users who only know a little VBA. It is most relevant when a workbook has enough worksheets that dragging tabs individually becomes tedious. The transcript describes a workbook with 50 or 100 sheets as the motivating case, but does not establish a minimum number for the method to be useful.

The demonstration does not state a required Excel license, subscription plan, operating system, or rollout status. It also does not identify version requirements for the SORT function used by the macro. Because the code calls Application.WorksheetFunction.Sort, confirm that the Excel installation where you intend to run it supports that function before relying on this exact approach.

The presenter says he would normally put the code in a personal macro workbook or another file when he does not want macros stored in the workbook being organized. The transcript does not give detailed steps for setting up a personal macro workbook or discuss how an organization manages macro execution. If you are unfamiliar with VBA, test on a copy before applying a macro to a workbook you need to preserve.

How it works

The procedure uses an array as a temporary list of names. VBA arrays in this example are treated as column-oriented by the SORT function, so the function’s fourth argument, by_col, needs to be True. Leaving that argument out or setting it incorrectly can return the names in their existing order rather than sorting them as intended.

1. Open the Visual Basic Editor and insert a module. The demonstration uses a Sub procedure named arrangeMySheets.
2. Set a workbook variable to ActiveWorkbook and prepare a worksheet variable. Size a string array from 1 through wb.Sheets.Count so it can hold one name for each sheet.
3. Set the counter i to 1, then loop through wb.Sheets. For each sheet, store sht.Name in the array and increase i by 1. The transcript shows that forgetting to initialize i caused a debugging problem; setting it to 1 resolved that issue.
4. Declare sortedNames as a Variant and assign it the result of Application.WorksheetFunction.Sort(shtNames, , , True). The final True tells SORT to sort by column, which is the orientation needed for the array in this example.
5. Loop from 1 through the number of sheets. For each position, use the sheet named by sortedNames(i) and move it before wb.Sheets(i). Repeating this applies the alphabetical list to the workbook’s tab order.

The presenter uses the VBA watch window to inspect the original and sorted arrays while debugging. That is useful for checking whether the names have been collected and sorted before moving any sheets. After running the procedure, the tab names should appear in alphabetical order.

Limitations and open questions

The method depends on the active workbook reference, so the workbook that is active at run time determines which sheets are changed. The transcript does not show added safeguards to confirm the correct workbook is active, handle errors, or ask the user to approve the reorder. Consider those omissions before adapting the demonstrated logic for important files.

The video does not specify how the macro treats chart sheets or other non-worksheet sheet types, even though the collection loop refers to wb.Sheets and declares sht as Worksheet. It also does not explain how names with numbers, punctuation, or different capitalization are ordered. Those behaviors should not be assumed from the city-name example.

This approach changes tab positions; it does not rename sheets, color-code them, or alter their contents. The presenter mentions those as possible extensions, but does not demonstrate their implementation. The source material also does not verify compatibility across Excel versions or provide an alternative for users who cannot run VBA, so those details remain open.

Sources

• Chandoo’s demonstration of sorting Excel sheets with VBA

Frequently asked questions

How do I sort Excel worksheet tabs alphabetically with VBA?

Collect the sheet names in an array, sort the array with Application.WorksheetFunction.Sort using by_col=True, then move each named sheet to its matching position. The active workbook is the target in the demonstrated approach.

Why does the VBA SORT function need by_col set to True?

The demonstration treats the array of sheet names as column-oriented. Setting the fourth SORT argument, by_col, to True makes the names sort in the intended order.

What Excel version or license is needed for this macro?

The source material does not specify an Excel version or license requirement. It uses the SORT function from VBA, so support for that function should be checked in the Excel installation where the macro will run.