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.
- Sub GroupSheetsByColor()
- Dim CurrentSheetIndex As Integer
- Dim PrevSheetIndex As Integer
- 'Set starting counts and start looping
- For CurrentSheetIndex = 1 To Sheets.Count
- For PrevSheetIndex = 1 To CurrentSheetIndex - 1
- 'Check Current sheet against Previous sheet to see if they match
- If Sheets(PrevSheetIndex).Tab.ColorIndex = _
- Sheets(CurrentSheetIndex).Tab.ColorIndex Then
- 'If so, move Current sheet before Previous
- Sheets(PrevSheetIndex).Move _
- End If
- 'Loop back around to iterate again
- Next PrevSheetIndex
- Next CurrentSheetIndex
- 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.