This is a functionality that is missing from Excel and can only be achieved by VBA code. Two such samples are listed below. The first will allow you to sort sheets alphabetically in Excel. The code allows you to manually change weather the sorting will be ascending or descending. The second sample allows you to sort the Sheets in a Workbook by the tab color.
Sub XET_Sheets_ABC_AZ() Dim wn(1024) Dim listi(1024) As String Dim hid(1024) As Integer Application.ScreenUpdating = False AtoZ = True wc = Worksheets.Count '# of sheets If wc < 2 Then Exit Sub End If For i = 1 To wc listi(i) = Sheets(i).Name Select Case Worksheets(i).Visible Case xlSheetVisible hid(i) = 0 Case xlSheetHidden hid(i) = 1 Case xlSheetVeryHidden hid(i) = 2 End Select Sheets(i).Visible = xlSheetVisible Next For i = 1 To wc For j = 1 To wc - 1 If AtoZ = True Then If Sheets(j).Name > Sheets(j + 1).Name Then Sheets(j + 1).Move before:=Sheets(j) End If Else If Sheets(j + 1).Name > Sheets(j).Name Then Sheets(j + 1).Move before:=Sheets(j) End If End If Next Next i For i = 1 To wc Select Case hid(i) Case 1 Sheets(listi(i)).Visible = xlSheetHidden Case 2 Sheets(listi(i)).Visible = xlSheetVeryHidden End Select Next Application.ScreenUpdating = True End Sub
The AtoZ (Line 7) functionality allows you to set the sort order Ascending or descending
The following code gives you the ability to sort sheets by tab color
Sub XET_Sheets_Color() Dim listi(1024) As String Dim hid(1024) As Integer Application.ScreenUpdating = False Dim wn(1024) ascending = False wc = Worksheets.Count '#ofSheets If wc < 2 Then Exit Sub End If For i = 1 To wc listi(i) = Sheets(i).Name Select Case Worksheets(i).Visible Case xlSheetVisible hid(i) = 0 Case xlSheetHidden hid(i) = 1 Case xlSheetVeryHidden hid(i) = 2 End Select Sheets(i).Visible = xlSheetVisible Next For i = 1 To wc For j = 1 To wc - 1 If narascajoce = True Then If Sheets(j).Tab.Color > Sheets(j + 1).Tab.Color Then Sheets(j + 1).Move before:=Sheets(j) End If Else If Sheets(j + 1).Tab.Color > Sheets(j).Tab.Color Then Sheets(j + 1).Move before:=Sheets(j) End If End If Next Next i For i = 1 To wc Select Case hid(i) Case 1 Sheets(listi(i)).Visible = xlSheetHidden Case 2 Sheets(listi(i)).Visible = xlSheetVeryHidden End Select Next Application.ScreenUpdating = True End Sub
It’s quite similar to the code above and it also has an Ascending TRUE and FALSE options although the discussion as to what ascending by color means could be quite long.
Both codes can also be found in an Xnet Excel Tools AddIn for Excel which is free and can be downloaded here.
The code bellow asks for a folder location, and then for a ...
This VBA code saves each Worksheet in the active Excel Workbook ...
This is a short but sweet VBA code that let’s you open all ...
Custom lists are very useful in Excel because they are a quick ...