Use VBA to select multiple worksheets based on tab color

[drewbny]

Is there a way you can select multiple worksheets based on tab color, or a way you can select which tabs you want to work with using a list? Using the traditional method of using control and click is too time consuming. Im not proficient in VBA and would like to know how this can be done.

 

[Marcelo Branco]

Hi,

A very simple example of a procedure using an array of worksheets-names.

(use a test workbook, please)

Public Sub Test1()
    
    With ThisWorkbook.Sheets(Array("Sheet1", "Sheet2"))
        .Item(1).Range("A1").Value = "Hello World"
        .Item(2).Range("B1").Value = "Hello World"
    End With
        
End Sub

HTH

M.

 

[Ruddles]

In your workbook, press Alt-F11 . If you can't see the VBA Project tree view, press Ctrl-R. Go Insert > Module: this will create a module probably called Module1. Double-click the name in the VBA Project tree view and a code window will open up. Replace the contents of this window with the following:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Public Sub SelectByTabColor()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim wsNames() As String
  Dim wsColor() As Integer
  Dim ws As Worksheet
  Dim ind As Integer
  
  ReDim wsNames(0)
  ReDim wsColor(0)
  wsNames(0) = ActiveSheet.Name
  wsColor(0) = ActiveSheet.Tab.ColorIndex
  
  For Each ws In ThisWorkbook.Sheets
    If ws.Tab.ColorIndex = wsColor(0) Then
      ReDim Preserve wsNames(UBound(wsNames) + 1)
      ReDim Preserve wsColor(UBound(wsColor) + 1)
      wsNames(UBound(wsNames)) = ws.Name
      wsColor(UBound(wsColor)) = ws.Tab.ColorIndex
    End If
  Next ws[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]  Sheets(wsNames).Select
   
End Sub[/SIZE][/FONT]

Ensure macros are enabled - save the workbook as a .xlsm file. When you run this code, it will select every worksheet whose tab color is the same as the worksheet which was selected when you ran the code. To run it, select one of the worksheets whose color you want to select by, then in go Developer > Macros, click SelectByTabColor and Run.

Shout if you experience any problems.

 

[drewbny]

Thanks Alot. it works great!!

 

[drewbny]

In your workbook, press Alt-F11 . If you can't see the VBA Project tree view, press Ctrl-R. Go Insert > Module: this will create a module probably called Module1. Double-click the name in the VBA Project tree view and a code window will open up. Replace the contents of this window with the following:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Public Sub SelectByTabColor()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim wsNames() As String
  Dim wsColor() As Integer
  Dim ws As Worksheet
  Dim ind As Integer
  
  ReDim wsNames(0)
  ReDim wsColor(0)
  wsNames(0) = ActiveSheet.Name
  wsColor(0) = ActiveSheet.Tab.ColorIndex
  
  For Each ws In ThisWorkbook.Sheets
    If ws.Tab.ColorIndex = wsColor(0) Then
      ReDim Preserve wsNames(UBound(wsNames) + 1)
      ReDim Preserve wsColor(UBound(wsColor) + 1)
      wsNames(UBound(wsNames)) = ws.Name
      wsColor(UBound(wsColor)) = ws.Tab.ColorIndex
    End If
  Next ws[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]  Sheets(wsNames).Select
   
End Sub[/SIZE][/FONT]

Ensure macros are enabled - save the workbook as a .xlsm file. When you run this code, it will select every worksheet whose tab color is the same as the worksheet which was selected when you ran the code. To run it, select one of the worksheets whose color you want to select by, then in go Developer > Macros, click SelectByTabColor and Run.

Shout if you experience any problems.

Hello, this code works on smaller files , but on other files I get a Error that says " Error 9 Subscript out of range". can you please advise ?

 

[Ruddles]

Smaller in terms of file size or smaller in terms of the number of worksheets?

When it errors, it should offer you a choice to "End or Debug" - select 'Debug'. The code window opens with the faulty line of code highlighted in yellow. Which line is it?

 

[drewbny]

Hi, this is the line with the error

Sheets(wsNames).Select

 

[drewbny]

HI , originally I was running this macro from my personal macro workbook. I also tried running this code inside the workbook I am using and this time I got a runtime 1004 error on the same line of code

Sheets(wsNames).Select

 

[Ruddles]

Just "runtime 1004" - no other information? I'm stumped and I can't reproduce it here. How many sheets do you have in your workbook?

Try adding these lines:-

[COLOR=blue][B]  Debug.Print "---"
  For Each ws In ThisWorkbook.Sheets: Debug.Print ws.Name; ",";: Next ws: Debug.Print
  For ind = 0 To UBound(wsNames): Debug.Print wsNames(ind); ",";: Next ind: Debug.Print
[/B][/COLOR]  
  Sheets(wsNames).Select

The next time it happens, go to VBA and open the immediate window (Ctrl-G): the penultimate should be a list of all the sheets in your workbook and the last one should be a list of the sheet you were in followed by the ones which have been selected.

Do they match what you expected?

 

[drewbny]

i don't have the workbook in front of me since I'm not at work. Could the problem be that i have some of the worksheets hidden?