How to sort worksheet tabs by color in Excel?

When working with large Excel workbooks containing multiple worksheets, assigning colors to worksheet tabs helps categorize and organize them visually. However, Excel does not provide a built-in option to sort worksheet tabs by color automatically. In this article, we will explore two effective methods to sort worksheet tabs by color in Excel.

Sort worksheet tabs by color with VBA code

Sort worksheet tabs by color with Kutools for Excel

Sort worksheet tabs by color with VBA code

Using the following VBA code, you can sort the tabs by color. Please do as follows:

1. Click "Developer" > "Visual Basic", a new "Microsoft Visual Basic for applications" window will be displayed, click "Insert" > "Module", and input the following code into the Module:

VBA: Sort sheets by colors.

Sub SortWorkBookByColor()
'UpdatebyExtendoffice
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
    For i = 1 To Application.ActiveWorkbook.Worksheets.Count
        If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
            n = n + 1
            ReDim Preserve xArray1(1 To n)
            ReDim Preserve xArray2(1 To n)
            xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
            xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
        End If
    Next
    For i = 1 To n
        For j = i To n
            If xArray1(j) < xArray1(i) Then
                temp = xArray2(i)
                xArray2(i) = xArray2(j)
                xArray2(j) = temp
                temp = xArray1(i)
                xArray1(i) = xArray1(j)
                xArray1(j) = temp
            End If
        Next
    Next
    For i = n To 1 Step -1
        Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
    Next
End If
Application.ScreenUpdating = True
End Sub

2. Then press "F5" or click the "Run" button to execute the code. And all of the worksheet tabs have been sorted by color.

Sort worksheet tabs by color with Kutools for Excel

With Kutools, you can quickly reorder your tabs based on their colors, saving time and eliminating the need for manual sorting or VBA code sorting. In this section, we’ll show you how to use Kutools for Excel to sort worksheet tabs by color with ease.

If you have installed Kutools for Excel, you can sort worksheet tabs with following steps:

1. Click "Kutools Plus" > "Worksheet" > "Sort Sheets".

2. In the "Sort Sheets" dialog box, click "Color Sort" button, you will see the new tabs order in the "New sheet tabs order" pane.

3. And then click "OK" to sort the tabs.

This feature can quickly rearrange all worksheets in the active workbook. It supports several sorting types, including "Alpha Sort", "Alpha Numeric Sort", "Color Sort" and "Reverse". In addition, you can also "move up / down" worksheets, and reset the sorting.

Sorting worksheet tabs by color in Excel helps improve organization and makes navigating large workbooks easier. While Excel lacks a built-in option for this, this article talk about two useful methods for you. Choose the method that best suits your needs, and take full advantage of Excel's tab color feature to streamline your workflow! If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

Related article:

Sort worksheets in alphabetical / alphanumeric order in Excel