CODEWITHSUNDEEP
excelvba
Steev43230
Steev43230

Reputation: 1

Create a sheet and set its tab color

I am trying to create sheets and, as each one is created, set its tab color. I would prefer to do this as soon as the sheet is created, as their names change over time and I don't want to affect other sheets in the workbook. Here is the code (using two created sheets as examples instead of the actual 20 or so that are created).

Problem is it throws a "Subscript out of range" error, even though the newly created sheet does exist. Code below - can anyone help?

Sub CreateSheets()
'Place each new sheet immediately before the sheet named Summary
'result = MsgBox("Have you" & vbCrLf & "(1) reset the date periods on the template," & vbCrLf & "(2) confirmed that the first data row is set to 13 in Column O, and" & vbCrLf & "(3) copied the overflow hours from last period to this period, updating the list of names (and the code) as necessary?", vbYesNo + vbQuestion, "Readiness Check")
'If result = vbYes Then
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Sheet Template")
    
    ws1.Copy ThisWorkbook.Sheets("Summary")
        ActiveSheet.Name = "Green, Samuel" '__________
        Sheets("Green, Samuel").Tab.ColorIndex = 0 '<------------ERROR OCCURS HERE
        
    ws1.Copy ThisWorkbook.Sheets("Summary")
        ActiveSheet.Name = "Smith, Michael" '________________
        Sheets("Smith, Michael").Tab.ColorIndex = 0
 'Else
    'End If
 End Sub

Upvotes: 0

Views: 81

Answers (2)

Steev43230
Steev43230

Reputation: 1

Thank you all. This code worked:

ActiveSheet.Tab.ColorIndex = 1

Upvotes: 0

BigBen
BigBen

Reputation: 50008

The problem is .Tab.ColorIndex = 0.

From the Tab.ColorIndex docs:

The color is specified as an index value in the current color palette from 1 to 56 or the XlColorIndex value xlColorIndexNone.

Use a valid value instead of 0.

Upvotes: 2

Related Questions