VBA: Creating & naming worksheets based on a template sheet

[JKast]

Hi all,

I'm currently using the code below to create worksheets that are formatted like the Template worksheet, and then named based on the title in each cell within the range Data1. This works prefectly as it copies the value of the name in the range to a newly created worksheet. Is there a way to copy the format of the cell too?

For example, Within my range Data1 are numbers from 1-30. The first 1-15 are colored red and 16-30 are blue. When I run the code to create worksheets 1-30, is there a way to also include the color of the cell so that worksheet tabs 1-15 would be red and 16-30 would be blue?

Public Sub MakeDestNameSheets()
Dim cell As Variant
For Each cell In Range("Data1")

Worksheets("Template").Copy after:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = cell.Value

End With

Next cell


End Sub

 

[jbeaucaire]

Something like this:

Public Sub MakeDestNameSheets()
Dim cell As Range
    
    For Each cell In Range("Data1")
        Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
            With ActiveSheet
                .Name = cell.Value
                .Tab.ColorIndex = cell.Interior.ColorIndex
            End With
    Next cell
End Sub

 

[JKast]

That works perfectly, thanks a bunch!