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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[SP]

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

Steve

"Doug Glancy" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>