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