Macro to sort excel sheets by name according to a custom order list

Hello Community, I am looking to write a simple macro that will look at all the sheet names in my workbook and then rearrange them according to a custom order. For Example: - The workbook has ...

developer

excel

Formulas and Functions

Macros and VBA

peiyezhu

Jun 28, 2023

Sub RearrangeSheets()
    Dim ws As Worksheet
    Dim orderList() As Variant
    Dim cell As Range
    Dim i As Long
    
    ' Define the custom order list from range A1's current region
    orderList = Range("A1").CurrentRegion.Value
    
    ' Disable screen updating to improve performance
    Application.ScreenUpdating = False
    
    ' Loop through each cell value in the custom order list (backwards)
    For i = UBound(orderList, 1) To LBound(orderList, 1) Step -1
        ' Find the sheet with the corresponding name
        Set ws = Worksheets(orderList(i, 1))
        On Error GoTo 0
        
        ' Move the sheet to the desired position
        If Not ws Is Nothing Then
            ws.Move Before:=Worksheets(1)
        End If
    Next i
    
    ' Enable screen updating again
    Application.ScreenUpdating = True
End Sub

Antonino2023

peiyezhu

Bronze Contributor

Jun 27, 2023

Sub RearrangeSheets()
    Dim ws As Worksheet
    Dim orderList() As Variant
    Dim i As Long
    
    ' Define the custom order list
    orderList = Array("D-Sheet", "B-Sheet", "F-Sheet", "A-Sheet", "C-Sheet")
    
    ' Disable screen updating to improve performance
    Application.ScreenUpdating = False
    
    ' Loop through each sheet name in the custom order list
    For i = LBound(orderList) To UBound(orderList)
        ' Find the sheet with the corresponding name
       ' On Error Resume Next
        Set ws = Worksheets(orderList(i))
        On Error GoTo 0
        
        ' Move the sheet to the desired position
        If Not ws Is Nothing Then
            ws.Move Before:=Worksheets(1)
        End If
    Next i
    
    ' Enable screen updating again
    Application.ScreenUpdating = True
End Sub

Antonino2023

Brass Contributor

Jun 28, 2023

peiyezhu

It almost works! However it is ordering them in the opposite order. Instead of D, B, F, A, C, it is leaving them as C, A, F, B, D. Is there a simple fix for this to your existing code?

Forum Discussion

Macro to sort excel sheets by name according to a custom order list

Resources