sorting the tabs

[SP]

Hi

Say, a workbook has 12 worksheets, and their tabs are color coded - 3
different colors for 3 groups of tab. The order of color on each tab is
random. The objective is to move the tabs so that the colors are grouped
together - no matter which one is first.

For example:
Random colored tabs:
red,green,blue,red,green,blue,red,green,blue,red,green,blue
Sorted colored tabs:
red,red,red,red,green,green,green,green,blue,blue,blue,blue

Note: Green or blue can be first ... it doesn't matter.

Please help with some sample code or pseudo code.

Thank you
Steve

 

[Doug Glancy]

SP,

This is modified from Chip Pearson's code here:

http://www.cpearson.com/excel/sortws.htm

Sub SortWorksheets()

Dim N As Long
Dim M As Long
Dim SortDescending As Boolean

SortDescending = False

With ActiveWorkbook.Sheets
For M = 1 To .Count
For N = M To .Count
If SortDescending = True Then
If Worksheets(N).Tab.ColorIndex >
Worksheets(M).Tab.ColorIndex Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If Worksheets(N).Tab.ColorIndex <
Worksheets(M).Tab.ColorIndex Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M
End With

End Sub

hth,

Doug

 

[SP]

Thank you very much for the fast response. The code really helps.

Steve