How to Sort Excel Sheets Alphabetically Using VBA

Organizing sheets in Excel manually can be tedious, especially if you’re dealing with a large number of them.

In this post, we’ll explore how to automate the process of sorting Excel sheets alphabetically using VBA. By leveraging a simple VBA script, Excel will reorder the sheets for you, saving time and effort.

Setting Up the VBA Script

The first step in this process is to set up the VBA environment. If you’re unfamiliar with how to enable or use VBA in Excel, it is essential to begin by enabling the Developer tab in Excel.

1. Open Excel, click on “File” and go to “Options.”
2. From the “Customize Ribbon” option, check the box for the “Developer” tab, and then click OK.
3. To open the VBA editor, click on the Developer tab and then select “Visual Basic.”

Breaking Down the Code (Line-by-Line)

The macro begins by counting the total number of sheets in the workbook.

total_sheets = Sheets.Count

This sets up the outer loop, which will iterate over each sheet in the workbook.

For outerloop = 1 To total_sheets

Within the outer loop, an inner loop is initiated to compare the sheet names against each other.

For innerloop = 1 To outerloop

The macro checks if the sheet in the outer loop should come before the sheet in the inner loop alphabetically. The comparison is done by converting both sheet names to uppercase to make it case-insensitive.

If UCase(Sheets(outerloop).Name) < UCase(Sheets(innerloop).Name) Then

If the sheet in the outer loop comes alphabetically before the sheet in the inner loop, the macro moves it to the correct position by placing it before the sheet in the inner loop.

Sheets(outerloop).Move before:=Sheets(innerloop)

After the comparison and possible move, the inner loop continues until all sheets are checked. The outer loop then progresses to the next sheet.

Next innerloop
Next outerloop

This process continues until all sheets in the workbook are alphabetically sorted.

Full Code Example

Below is the entire VBA script for you to copy and paste into your Excel VBA editor:

Option Explicit

Private Sub Button1_Click()
    Dim total_sheets As Integer
    Dim outerloop As Integer
    Dim innerloop As Integer
    
    total_sheets = Sheets.Count
    For outerloop = 1 To total_sheets
        For innerloop = 1 To outerloop
            
            If UCase(Sheets(outerloop).Name) < UCase(Sheets(innerloop).Name) Then
                Sheets(outerloop).Move before:=Sheets(innerloop)
            End If
            
        Next innerloop
    Next outerloop
End Sub

Running the Code

To execute the script:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Paste the code in a new module.
3. Create a button on your worksheet and assign the macro to it.
4. Click the button, and your sheets will be sorted alphabetically.

Flow Chart Of The Code

Result

Before

Notice that each sheets are saved aphabetically and are not in order

After

Now notice that all the sheet are arranged well aphabetically after running the code

Conclusion

Using this simple VBA script, you can quickly sort your Excel sheets alphabetically without manually dragging them around. This automated approach improves efficiency, especially for workbooks with many sheets. By integrating VBA into your workflow, tasks like these can be automated, saving time and ensuring consistency across multiple projects.

Resources
For more information about using VBA in Excel, visit Microsoft’s official VBA documentation. and Pillow

if you like this blog post on How to Sort Excel Sheets Alphabetically Using VBA, then you would love our other blog post on How to Combine Data from Multiple Sheets into a MasterSheet