Forum Discussion

Antonino2023

Brass Contributor

Jun 27, 2023

Solved

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
Also, how would I specify "orderList" to create the array from a range instead? My orderlist is over 100 so I cannot paste it into the module. For example, I would want it to use the orderlist given by the cells in the current region of A1. Range("A1").CurrentRegion

peiyezhu

Bronze Contributor

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

Antonino2023
Brass Contributor
Jun 28, 2023
peiyezhu

This is exactly what I was looking for, thank you!
- peiyezhu
  Bronze Contributor
  Jun 29, 2023
  You are welcome