Format Column Labels

[barim]

I have this piece of code that I would like to improve. There must be a better way to do it.

Appreciate any help on this.

Thanks.

ActiveSheet.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Products1"
    Columns(1).EntireColumn.Insert
    Columns(1).Cells(1, 1).Value = "ID"
    Columns(7).Cells(1, 1).Value = "Column1"
    Columns(7).Cells(1, 1).Interior.ColorIndex = 27
    Columns(7).Cells(1, 1).Font.Bold = True
    Columns(8).Cells(1, 1).Value = "Column2"
    Columns(8).Cells(1, 1).Interior.ColorIndex = 27
    Columns(8).Cells(1, 1).Font.Bold = True
    Columns(9).Cells(1, 1).Value = "Column3"
    Columns(9).Cells(1, 1).Interior.ColorIndex = 27
    Columns(9).Cells(1, 1).Font.Bold = True
    Columns(10).Cells(1, 1).Value = "Column4"
    Columns(10).Cells(1, 1).Interior.ColorIndex = 27
    Columns(10).Cells(1, 1).Font.Bold = True
    Columns(11).Cells(1, 1).Value = "Column5"
    Columns(11).Cells(1, 1).Interior.ColorIndex = 27
    Columns(11).Cells(1, 1).Font.Bold = True
    Columns(12).Cells(1, 1).Value = "Date"
    Columns(12).Cells(1, 1).Interior.ColorIndex = 27
    Columns(12).Cells(1, 1).Font.Bold = True
    Columns(12).Cells(2, 1).Value = Format(Now(), "mm/dd/yyyy")

[Paul_Hossler]

Many ways -- here's one

Option Explicit

Sub test()
    'ActiveSheet.Copy After:=Sheets(Sheets.Count)
    'ActiveSheet.Name = "Products1"
    'Columns(1).EntireColumn.Insert
    
    Dim aryColHeaders As Variant
    'base = 0
    aryColHeaders = Array("Column1", "Column2", "Column3", "Column4", "Column5", "Date")
    
    With Worksheets("Products1")
        .Cells(1, 1).Value = "ID"
        With .Cells(1, 7).Resize(1, UBound(aryColHeaders) + 1)
            .Value = aryColHeaders
            .Interior.ColorIndex = 27
            .Font.Bold = True
        End With
        .Cells(2, 12).Value = Format(Now(), "mm/dd/yyyy")
    End With
End Sub

[barim]

Thank you Paul. It works.