Rearrange Excel Sheets in Custom or Alphabetic Order with VBA

Custom Order or Alphabetical Order

This page has VBA code that can arrange worksheets in a Excel file in:

1. Custom Order as per user preference
2. Alphabetic Order of the Sheet Names

Lets get the code.

VBA Code – Arrange Sheets in Custom Order

To make the below code work, You have to specify the order of each sheet already in this format.

It is assumed that You create a sheet with name “Settings” and mentioned the order as above for the below code to work.

Here is the code.

Sub Rearrange_Sheet_Custom_Order()
    'Declare Variables - Officetricks.com
    Dim i As Double, j As Double
    Dim sheetName As String, shSettings As Worksheet
    Dim sheetOrder As Double, sheetsCount As Double
    
    'Initialize Variables
    Set shSettings = ThisWorkbook.Sheets("Settings")
    sheetsCount = ThisWorkbook.Sheets.Count
    
    'Loop Each sheet
    For i = 1 To sheetsCount
        j = 1
        Do While shSettings.Cells(j, 1) <> ""
            sheetName = shSettings.Cells(j, 1)
            sheetOrder = shSettings.Cells(j, 2)
            
            'Arrange as per the Order
            If i = sheetOrder Then
                ThisWorkbook.Sheets(sheetName).Move before:=ThisWorkbook.Sheets(sheetOrder)
                Exit Do
            End If
            
            j = j + 1
        Loop
    Next
End Sub

You can see that there is no predefined function for this purpose. We just use the worksheet.Move function to achieve this.

VBA Code Sort Sheets Alphabetic Order

This code will read all the sheet names and arrange them in Alphabetic order.  In here there is no need to mention any specific custom order for the sheet.

Sub Rearrange_Sheet_Alphabetic_Order()
    'Declare Variables - Officetricks.com
    Dim i As Double, j As Double
    Dim sheetName1 As String, sheetName2 As String
    Dim sheetsCount As Double
    
    'Initialize variables
    sheetsCount = ThisWorkbook.Sheets.Count
    
    'Loop Each sheet
    For i = 1 To sheetsCount
        sheetName1 = ThisWorkbook.Sheets(i).Name
        minName = sheetName1
        For j = (i + 1) To sheetsCount
            sheetName2 = ThisWorkbook.Sheets(j).Name
            
            'Check for Alphabetic Order
            If sheetName2 < minName Then
                minName = sheetName2
            End If
            
        Next j
        
        'Move Smallest to Current Position
        If minName <> sheetName1 Then
            ThisWorkbook.Sheets(minName).Move before:=ThisWorkbook.Sheets(i)
        End If
    Next i
End Sub

After you execute the code, you could see that the sheets are arranged in alphabetic order of their names,