I am a VBA novice writing an Excel 2002 template. I wish to add a standard
header and footer to any new worksheet that is added. My attempt to achieve
this has fallen at the first hurdle! Can anyone help me with this?

My code is:-

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
Dim fs As String
fs = "Ariel Size 8" ' Font Size
fc = "FF0000" ' Colour = Red
Application.ScreenUpdating = False
Set ws = ActiveWorkbook.Worksheets
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = "My Company Name" & fs & "K(fc)"
.RightHeader = ""
.LeftFooter = "&F" & Chr(10) & "&A" & fs & "K(fc)" 'Document Name and
Workbook Name
.CenterFooter = ""
.RightFooter = "&D" & Chr(10) & "&T" & fs & "K(fc)" 'Current date and
Current Time
End With
Set ws = Nothing
End Sub



Thanks in anticipation.