Let's Talk Excel

Sorting Sheets by name or tab color

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.

Sorting Sheets By Name

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

Sorting Sheets By Tab Color

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: