Add standard header and footer to new worksheet

G

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.
 
C

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]
 
G

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.

Chip Pearson said:
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.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top