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. :clap: