Forum Discussion
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 ...
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
Sub SortSelectedSheets()
Dim selectedSheets
Set selectedSheets = ActiveWindow.SelectedSheets
Dim i As Long, j As Long
For i = 1 To selectedSheets.Count
For j = 1 To selectedSheets.Count - 1
If UCase$(selectedSheets(j).Name) > UCase$(selectedSheets(j + 1).Name) Then
selectedSheets(j).Move after:=selectedSheets(j + 1)
End If
Next
Next
MsgBox "The selected tabs have been sorted from A to Z."
End Sub
