Custom Order or Alphabetical Order
This page has VBA code that can arrange worksheets in a Excel file in:
- Custom Order as per user preference
- 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,