How to sort sheets based on custom criteria using VBA?

Sorting Excel sheets based on custom criteria using VBA requires iterating over the worksheets and rearranging them according to a specified order. Below is a step-by-step guide and a sample VBA macro to achieve this:

Step-by-Step Guide:

Sub SortWorksheetsByCustomOrder()
    Dim ws As Worksheet
    Dim i As Integer, j As Integer
    Dim customOrder As Variant
    Dim sheetCount As Integer
    Dim foundIndex As Integer

    ' Specify your custom order here
    customOrder = Array("Sheet3", "Sheet1", "Sheet2")
    sheetCount = ThisWorkbook.Worksheets.Count

    ' Iterate through the custom order array
    For i = LBound(customOrder) To UBound(customOrder)
        ' Search for the sheet with the matching name
        For j = 1 To sheetCount
            Set ws = ThisWorkbook.Worksheets(j)
            If ws.Name = customOrder(i) Then
                foundIndex = j
                Exit For
            End If
        Next j
        
        ' Move the found sheet to the position as per the custom order
        ws.Move Before:=ThisWorkbook.Worksheets(i + 1)
    Next i
End Sub

Define Your Custom Order: Decide the order in which you want the sheets to be sorted. This could be a list of sheet names in the desired sequence.
Access the VBA Editor:

Press `ALT + F11` to open the VBA editor in Excel.

Insert a New Module:

In the VBA editor, right-click on any of the objects for your workbook in the Project Explorer.
Select `Insert` > `Module` to create a new module.

Write the VBA Code: Copy and paste the sample code below into the module.

Explanation:

Custom Order: The `customOrder` array holds the names of the worksheets in the order you want them sorted.
For Loops: The outer loop iterates through the `customOrder` array, while the inner loop searches for each sheet name within the workbook.
Move Method: Once a matching sheet is found, `ws.Move Before:=ThisWorkbook.Worksheets(i + 1)` is used to move the sheet to the desired position.

Running the Macro:

Close the VBA editor and return to Excel.
Press `ALT + F8` to open the ‘Macro’ dialog box.
Select `SortWorksheetsByCustomOrder` and click ‘Run’.

Ensure that your workbook’s sheets match the names specified in the `customOrder` array, and that no sheets are protected. Adjust the `customOrder` list to reflect your desired order. This solution assumes you want to sort all sheets. If you need to exclude certain sheets, add condition checks before the final move operation.

facebooklinkedin