Sort Worksheet by Color VBA

Hi All,

Here is routine to sort the worksheets by tab color

Code:

---

Sub SortWorksheetsByColor(Optional ByVal SortByAsc As Boolean = True)
   
    Dim i          As Long
    Dim j          As Long
    Dim ShtC()      As Long
    Dim ShtN()      As String
    Dim t, n        As Long
    Dim lngSU      As Long
   
    'Developed by Kris @ ExcelFox.com
    'Works XL 2002 or later(Tested only 2007)
   
    With Application
        lngSU = .ScreenUpdating
        .ScreenUpdating = False
    End With
   
    If Val(Application.Version) >= 10 Then
        With ThisWorkbook
            For i = 1 To .Worksheets.Count
                If .Worksheets(i).Visible = -1 Then
                    n = n + 1
                    ReDim Preserve ShtC(1 To n)
                    ReDim Preserve ShtN(1 To n)
                    ShtC(n) = .Worksheets(i).Tab.Color
                    ShtN(n) = .Worksheets(i).Name
                End If
            Next
            For i = 1 To n
                For j = i To n
                    If ShtC(j) < ShtC(i) Then
                        t = ShtN(i)
                        ShtN(i) = ShtN(j)
                        ShtN(j) = t
                        t = ShtC(i)
                        ShtC(i) = ShtC(j)
                        ShtC(j) = t
                    End If
                Next
            Next
            If SortByAsc Then
                For i = n To 1 Step -1
                    .Worksheets(CStr(ShtN(i))).Move before:=.Worksheets(1)
                Next
            Else
                For i = n To 1 Step -1
                    .Worksheets(CStr(ShtN(i))).Move after:=.Worksheets(.Worksheets.Count)
                Next
            End If
        End With
    End If
   
    Application.ScreenUpdating = lngSU
   
End Sub

---

Call the routine like

For ascending

Code:

---

SortWorksheetsByColor True

---

for descending

Code:

---

SortWorksheetsByColor False

---

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA