Add standard header and footer to new worksheet

[Grey Old Man]

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.

 

[Chip Pearson]

In the procedure you have, the Sh variable in the parameter list is a
reference to the newly created worksheet. You should use that
reference rather than the Ws reference. Change

With ws.PageSetup

to

With Sh.PageSetup

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Grey Old Man]

Thanks. The following code works OK, but how do I add the font attributes?

Private Sub Workbook_NewSheet(ByVal sh As Object)
Dim fs As String
Dim fn As String
Dim fc As String
fs = "8" ' Font Size
fn = "Arial" ' Font Name
fc = "FF0000" ' Font Colour = Red
Application.ScreenUpdating = False
With sh.PageSetup
.LeftHeader = ""
.CenterHeader = "My Company Name"
.RightHeader = ""
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = ""
.RightFooter = "&D" & Chr(10) & "&T"
End With
End Sub


Thanks in anticipation.

In the procedure you have, the Sh variable in the parameter list is a
reference to the newly created worksheet. You should use that
reference rather than the Ws reference. Change

With ws.PageSetup

to

With Sh.PageSetup

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

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.

.