Selected Answer
Leap
You added a sheet but didn't name it "ws" so the With selection didn't work.
Try this instead, noting that I made the header font white (for better contrast) and used autofit once the headers where in place (these changes in bold):
Sub InsertHeaders()
Dim ws As Worksheet
Set ws = Sheets.Add
With ws
.Range("A1:f12").Borders.Weight = xlMedium
.Range("A1:f12").HorizontalAlignment = xlCenter
With .Cells(1).Resize(1, 6)
.Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
.Interior.ColorIndex = 53
.Font.Bold = True
.Font.Color = vbWhite
.EntireColumn.AutoFit
End With
End With
End Sub
If you want EVERY new sheet in that workbook to have that format, use the Workbook event NewSheet instead. At the ThisWorkbook level, add this code (changes from above in bold) then every time you press the + button (to the right of the the sheet tabs) a sheet like this will appear after the existing ones:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Sh
.Move After:=Sheets(Sheets.Count)
.Range("A1:f12").Borders.Weight = xlMedium
.Range("A1:f12").HorizontalAlignment = xlCenter
With .Cells(1).Resize(1, 6)
.Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")
.Interior.ColorIndex = 53
.Font.Bold = True
.Font.Color = vbWhite
.EntireColumn.AutoFit
End With
End With
End Sub
Hope this works for you.
