Group Sheets by Color

Just recently, I encountered a workbook where the worksheet tabs were colored. The colors apparently had some meaning, indicating that each colored tab is somehow related to another tab. So of course, I wanted to sort the tabs by color. It goes without saying that doing this manually was out of the question; there were too many tabs, I was too lazy, and the hour was late. So VBA to the rescue!

This macro iterates through the sheets in the workbook, comparing the tab color index of current sheet to that of the previous one.

If the previous sheet has the same color index number as the current sheet, then we move the current sheet before it.

By the time all the iterations are done, all sheets are grouped together based on their tab colors.


.

  1. Sub GroupSheetsByColor()
  2.    
  3.     Dim CurrentSheetIndex As Integer
  4.     Dim PrevSheetIndex As Integer
  5.  
  6. 'Set starting counts and start looping
  7.    For CurrentSheetIndex = 1 To Sheets.Count
  8.     For PrevSheetIndex = 1 To CurrentSheetIndex - 1
  9.  
  10. 'Check Current sheet against Previous sheet to see if they match
  11.    If Sheets(PrevSheetIndex).Tab.ColorIndex = _
  12.     Sheets(CurrentSheetIndex).Tab.ColorIndex Then
  13.  
  14. 'If so, move Current sheet before Previous
  15.    Sheets(PrevSheetIndex).Move _
  16.     Before:=Sheets(CurrentSheetIndex)
  17.     End If
  18.  
  19. 'Loop back around to iterate again
  20.    Next PrevSheetIndex
  21.     Next CurrentSheetIndex
  22.  
  23. End Sub

You won’t use this macro every day, but it is one of those macros you can keep around for special occasions.

Like a fine bottle of wine, or bean dip.

 

 

4 thoughts on “Group Sheets by Color

Leave a Reply

Your email address will not be published. Required fields are marked *