sorting the tabs

  • Thread starter Thread starter SP
  • Start date Start date
S

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
 
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
 
Back
Top