Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add standard header and footer to new worksheet
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add standard header and footer to new worksheet
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] On Tue, 11 May 2010 07:29:01 -0700, Grey Old Man wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add standard header and footer to new worksheet
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" wrote: 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] On Tue, 11 May 2010 07:29:01 -0700, Grey Old Man wrote: 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show where worksheet is saved in the header/footer | Excel Discussion (Misc queries) | |||
Duplicate A5 worksheet with header & footer onto an A4 original | Excel Discussion (Misc queries) | |||
how to include a variable in the header and/or footer of a worksheet | Excel Discussion (Misc queries) | |||
Is it possible to copy a header/footer from one worksheet to anoth | Excel Discussion (Misc queries) | |||
Landscape worksheet with portrait header/footer | Excel Discussion (Misc queries) |