How to Group Worksheets by Color

[jamesexcel1970]

Hello Experts!

we work with huge workbooks and keep on adding them as per the requirment with some color code, can we group them by color by any chance..we are doing it manually by dragging sheets.

Thanking you in advance!

 

[Monty]

James..Can I request u to upload sample else need to guess and may not suit your requirements.

 

[Monty]

Try this..Not

Sub Group_Testing()

'Step 1: Declare your Variables
    Dim CurrentSheetIndex As Integer
    Dim PrevSheetIndex As Integer

'Step 2: Set the starting counts and start looping
    For CurrentSheetIndex = 1 To Sheets.Count
    For PrevSheetIndex = 1 To CurrentSheetIndex - 1

'Step 3: Check Current Sheet against Previous Sheet
    If Sheets(PrevSheetIndex).Tab.ColorIndex = _
    Sheets(CurrentSheetIndex).Tab.ColorIndex Then

'Step 4: If Move Current sheet Before Previous
    Sheets(PrevSheetIndex).Move _
    Before:=Sheets(CurrentSheetIndex)
    End If

'Step 5 Loop back around to iterate again
    Next PrevSheetIndex
    Next CurrentSheetIndex

End Sub

Tested

 

[jamesexcel1970]

Thanks for the replay..Will check and confirm.

 

[jamesexcel1970]

Hey champ.

It works tested with working with four sheets with colors.
need to check with the actual file on monday as logged off from sys.
Thank you so very much for your help.

 

[Monty]

Let me know any challenges on Monday.

 

[kelvin-lkh]

Hi Monty,

I tried. but sometimes need to run twice to achieve the result.

I have modified abit on the code as highlighted although it takes slightly longer time.


Sub Group_Testing()

'Step 1: Declare your Variables
Dim CurrentSheetIndex AsInteger
Dim PrevSheetIndex AsInteger

'Step 2: Set the starting counts and start looping
For CurrentSheetIndex = 1 To Sheets.Count -1
For PrevSheetIndex = 2 To Sheets.Count

'Step 3: Check Current Sheet against Previous Sheet If Sheets(PrevSheetIndex).Tab.ColorIndex = _
Sheets(CurrentSheetIndex).Tab.ColorIndex Then

'Step 4: If Move Current sheet Before Previous
Sheets(PrevSheetIndex).Move _
Before:=Sheets(CurrentSheetIndex)
EndIf

'Step 5 Loop back around to iterate again Next PrevSheetIndex
Next CurrentSheetIndex

EndSub