[Excel VBA] How to Sort All Worksheets by Name (Ascending Order)

2025年12月29日

When handling many sheets in Excel, the order of the tabs can become messy, making it difficult to find the sheet you need. Manually dragging and sorting them is very tedious when there are many sheets.

With VBA, you can automatically sort all worksheets alphabetically (in ascending order). In this article, I will explain the code to neatly organize your sheets using a basic sorting algorithm (Bubble Sort).

Sorting Process Flow

This macro operates in three steps:

Get a List of Sheet Names: First, store all sheet names existing in the current workbook into an array (a type of variable in VBA).
Sort the Array Alphabetically: Next, sort the sheet names stored in the array into ascending order within the program.
Physically Move the Sheets: Finally, move the actual sheets one by one using the .Move method according to the order of the sorted array.

The Completed VBA Code

Below is the VBA code to sort sheets by name.

Sub SortAllWorksheetsByName()

    ' Declare variables
    Dim sheetNames() As String
    Dim sheetCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim tempName As String
    
    '--- 1. Get all sheet names and store in an array ---
    sheetCount = ThisWorkbook.Worksheets.Count
    ReDim sheetNames(1 To sheetCount)
    
    For i = 1 To sheetCount
        sheetNames(i) = ThisWorkbook.Worksheets(i).Name
    Next i
    
    '--- 2. Sort sheet names in the array using Bubble Sort ---
    For i = 1 To sheetCount - 1
        For j = i + 1 To sheetCount
            ' Compare names; if the i-th name is greater, swap them
            If sheetNames(i) > sheetNames(j) Then
                tempName = sheetNames(j)
                sheetNames(j) = sheetNames(i)
                sheetNames(i) = tempName
            End If
        Next j
    Next i
    
    '--- 3. Move sheets according to the sorted array order ---
    Application.ScreenUpdating = False ' Suppress screen flickering
    
    For i = 1 To sheetCount
        ThisWorkbook.Worksheets(sheetNames(i)).Move After:=ThisWorkbook.Worksheets(sheetCount)
    Next i
    
    Application.ScreenUpdating = True
    
    MsgBox "All sheets have been sorted by name."

End Sub

Explanation of Key Points

1. Get a List of Sheet Names

In the first For loop, the code reads the names of all worksheets in the workbook and stores them sequentially in the sheetNames array variable.

2. Sort the Array (Bubble Sort)

For i = 1 To sheetCount - 1
    For j = i + 1 To sheetCount
        If sheetNames(i) > sheetNames(j) Then
            ' ... Swapping process ...
        End If
    Next j
Next i

This part uses a classic algorithm called Bubble Sort to arrange the strings in the array in ascending order. It uses a double loop to compare all elements in the array, and if the order is reversed, it swaps the values using a temporary variable tempName.

3. Move Sheets According to Sorted Order

For i = 1 To sheetCount
    ThisWorkbook.Worksheets(sheetNames(i)).Move After:=ThisWorkbook.Worksheets(sheetCount)
Next i

This is the core of the sorting logic. It processes the sorted sheetNames array from the beginning and moves each sheet to the very right end (after the last sheet) of the workbook.

For example, if the sorted array is (“A”, “B”, “C”):

First, sheet “A” moves to the end. The order becomes (B, C, A).
Next, sheet “B” moves to the end. The order becomes (C, A, B).
Finally, sheet “C” moves to the end. The order becomes (A, B, C), resulting in the correct sorted order.

Also, Application.ScreenUpdating = False is used to prevent the screen from flickering during the move process.