Today, I would like to show a code which I created to sort the worksheets in ascending or descending order in the current workbook. I have opted bubble sort to sort the names.
What did I do?
First, I counted the number of worksheets available in the current workbook. Then created an array and saved the worksheets’ names in the array, sorted the names in ascending or descending order (two codes – based on your requirement, you can choose any one) and moved the worksheets according to the sorted order. In the end, show/select the first worksheet.
Here goes the code:
Sub SortWorksheetNamesAscending() Dim NoOfSheets As Long, Counter As Long Dim Counter1 As Long, Counter2 As Long Dim TemporaryVariable As String Dim WkShtsNames() As String 'Count the number of sheets in the workbook NoOfSheets = ActiveWorkbook.Worksheets.Count 'Redefine and assign the names of the worksheets in WkShtsNames array ReDim WkShtsNames(1 To NoOfSheets) For Counter = 1 To NoOfSheets WkShtsNames(Counter) = ActiveWorkbook.Worksheets(Counter).Name Next Counter 'Sort the worksheets' names in ascending order For Counter1 = LBound(WkShtsNames) To UBound(WkShtsNames) For Counter2 = LBound(WkShtsNames) To UBound(WkShtsNames) If WkShtsNames(Counter1) < WkShtsNames(Counter2) Then TemporaryVariable = WkShtsNames(Counter1) WkShtsNames(Counter1) = WkShtsNames(Counter2) WkShtsNames(Counter2) = TemporaryVariable End If Next Counter2 Next Counter1 'Move the worksheets according to the sorted order For Counter = 1 To NoOfSheets If Counter = 1 Then If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _ ActiveWorkbook.Worksheets(1) End If ElseIf Counter = NoOfSheets Then If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _ ActiveWorkbook.Worksheets.Count End If Else ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _ After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets(Counter – 1).Name) End If Next Counter 'Select the first worksheet ActiveWorkbook.Worksheets(1).Select End Sub Sub SortWorksheetNamesDescending() Dim NoOfSheets As Long, Counter As Long Dim Counter1 As Long, Counter2 As Long Dim TemporaryVariable As String Dim WkShtsNames() As String ‘Count the number of sheets in the workbook NoOfSheets = ActiveWorkbook.Worksheets.Count ‘Redefine and assign the names of the worksheets in WkShtsNames array ReDim WkShtsNames(1 To NoOfSheets) For Counter = 1 To NoOfSheets WkShtsNames(Counter) = ActiveWorkbook.Worksheets(Counter).Name Next Counter ‘Sort the worksheets’ names in descending order For Counter1 = LBound(WkShtsNames) To UBound(WkShtsNames) For Counter2 = LBound(WkShtsNames) To UBound(WkShtsNames) If WkShtsNames(Counter1) > WkShtsNames(Counter2) Then TemporaryVariable = WkShtsNames(Counter1) WkShtsNames(Counter1) = WkShtsNames(Counter2) WkShtsNames(Counter2) = TemporaryVariable End If Next Counter2 Next Counter1 ‘Move the worksheets according to the sorted order For Counter = 1 To NoOfSheets If Counter = 1 Then If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _ ActiveWorkbook.Worksheets(1) End If ElseIf Counter = NoOfSheets Then If ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Index <> Counter Then ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _ ActiveWorkbook.Worksheets.Count End If Else ActiveWorkbook.Worksheets(WkShtsNames(Counter)).Move _ After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets(Counter – 1).Name) End If Next Counter ‘Select the first worksheet ActiveWorkbook.Worksheets(1).Select End Sub