Need help with a Macro or VB code for Header/Footer

[Guest]

Looking for a macro to enter a custom header and a footer

Header would have:

title of worksheet
job#
client name
date


footer would have:

file path
author
page# of total pages

I saw how to create file paths and dates but I'm trying to combo everything
into an easy to enter series of text boxes so our personnel don't have to
open "page setup" each time...
any suggestions would be appreciated
Thanks

 

[Guest]

From VBA help files:

Worksheets("Sheet1").PageSetup.CenterHeader = "&D page &P of &N"

You can also do:

Sub heddft()
Worksheets(1).PageSetup.CenterHeader = "&b &14 My Worksheet" _
& vbCrLf & "Job 1" & vbCrLf & "Some Client" & vbCrLf & "&d"
End Sub

For footer just substitute CenterFooter fow CenterHeader, or LeftHeader,
LeftFooter, etc. It is all the same syntax. The &b and &14 and &d inside
the quote marks set bold = true, Font size = 14 and Current date respectively.

 

[Guest]

Hi -
Here are a couple of options. Put either one in the ThisWorkbook module.

Because a long pathname can encroach on the center footer, the second
version 'stacks' the strings in the left footer.

Version1:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
'worksheet title in A1
.LeftHeader = Range("A1")
'job# in A2 and client in A3
.CenterHeader = Range("A2").Value & " --- " & Range("A3")
.RightHeader = Format(Date, "dd-mmm-yyyy")

.LeftFooter = ThisWorkbook.FullName & Chr(13) ' or ThisWorkbook.Name
.CenterFooter = ThisWorkbook.Author
.RightFooter = "Page &P of &N"
End With
End Sub

Version2:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
'worksheet title in A1
.LeftHeader = Range("A1")
'job# in A2 and client in A3
.CenterHeader = Range("A2").Value & " --- " & Range("A3")
.RightHeader = Format(Date, "dd-mmm-yyyy")

.LeftFooter = ThisWorkbook.FullName & Chr(13) & _
ThisWorkbook.Author & Chr(13) & _
"Page &P of &N"
.CenterFooter = ""
.RightFooter = ""
End With
End Sub

 

[Guest]

Hi,
thanks for the help but im not sure where this goes...where is the
ThisWorkbook Module?

Thanks

 

[Gord Dibben]

Right-click on the Excel Icon left of File on the menu bar or on the title bar
left of the filename if the sheet window is not maximized.

Select "View Code".

Paste the code into that module.


Gord Dibben MS Excel MVP

 

[Guest]

Thank you...all of you big help! 8)

 

[Guest]

I'm using the following per the instructions below, and located at: http://www.cpearson.com/excel/headfoot.htm

However the 2 rows highlighted in Red are not pulling the Data from cells B2 and B3.

Secondly I would like to make a Font Transition to 22PTS for for cells B2 and B3.


Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.Leftheader = "&B&18&""Arial"" Stock: "
Format (Worksheets("CALL_Profitability").Range("B2").Value)

ActiveSheet.PageSetup.Centerheader = "&B&18&""Arial"" Option: "
Format (Worksheets("CALL_Profitability").Range("B3").Value)

End Sub

Thanks,
Hank