Insert cell contents into header/footer

I would like to know how to insert the contents of a particular cell (in each
worksheet) into the header/footer for the corresponding worksheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = Range("A1").Value
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

"joeeng" <[email protected]> wrote in message
news:[email protected]...
> I would like to know how to insert the contents of a particular cell (in
each
> worksheet) into the header/footer for the corresponding worksheet.

I would like this routine to work for all worksheets in the workbook when the
"print entire workbook" selection is made. The routine below only does the
first worksheet footer when I print the entire workbook.

"Bob Phillips" wrote:

> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> With ActiveSheet.PageSetup
> .LeftFooter = Range("A1").Value
> End With
> End Sub
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
> --
> HTH
>
> Bob Phillips
>
> "joeeng" <[email protected]> wrote in message
> news:[email protected]...
> > I would like to know how to insert the contents of a particular cell (in
> each
> > worksheet) into the header/footer for the corresponding worksheet.
>
>
>

You can loop through the sheets

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.LeftFooter = wkSht.Range("A1").Value
Next wkSht
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"joeeng" <[email protected]> wrote in message news:[email protected]...
>I would like this routine to work for all worksheets in the workbook when the
> "print entire workbook" selection is made. The routine below only does the
> first worksheet footer when I print the entire workbook.
>
> "Bob Phillips" wrote:
>
>> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>> With ActiveSheet.PageSetup
>> .LeftFooter = Range("A1").Value
>> End With
>> End Sub
>>
>> 'This is workbook event code.
>> 'To input this code, right click on the Excel icon on the worksheet
>> '(or next to the File menu if you maximise your workbooks),
>> 'select View Code from the menu, and paste the code
>>
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "joeeng" <[email protected]> wrote in message
>> news:[email protected]...
>> > I would like to know how to insert the contents of a particular cell (in
>> each
>> > worksheet) into the header/footer for the corresponding worksheet.
>>
>>
>>

Thanks, that works.

"Ron de Bruin" wrote:

> You can loop through the sheets
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim wkSht As Worksheet
> For Each wkSht In ThisWorkbook.Worksheets
> wkSht.PageSetup.LeftFooter = wkSht.Range("A1").Value
> Next wkSht
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "joeeng" <[email protected]> wrote in message news:[email protected]...
> >I would like this routine to work for all worksheets in the workbook when the
> > "print entire workbook" selection is made. The routine below only does the
> > first worksheet footer when I print the entire workbook.
> >
> > "Bob Phillips" wrote:
> >
> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >> With ActiveSheet.PageSetup
> >> .LeftFooter = Range("A1").Value
> >> End With
> >> End Sub
> >>
> >> 'This is workbook event code.
> >> 'To input this code, right click on the Excel icon on the worksheet
> >> '(or next to the File menu if you maximise your workbooks),
> >> 'select View Code from the menu, and paste the code
> >>
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> "joeeng" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I would like to know how to insert the contents of a particular cell (in
> >> each
> >> > worksheet) into the header/footer for the corresponding worksheet.
> >>
> >>
> >>
>
>
>

Can I change the font characteristics in this routine?

"Ron de Bruin" wrote:

> You can loop through the sheets
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim wkSht As Worksheet
> For Each wkSht In ThisWorkbook.Worksheets
> wkSht.PageSetup.LeftFooter = wkSht.Range("A1").Value
> Next wkSht
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "joeeng" <[email protected]> wrote in message news:[email protected]...
> >I would like this routine to work for all worksheets in the workbook when the
> > "print entire workbook" selection is made. The routine below only does the
> > first worksheet footer when I print the entire workbook.
> >
> > "Bob Phillips" wrote:
> >
> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >> With ActiveSheet.PageSetup
> >> .LeftFooter = Range("A1").Value
> >> End With
> >> End Sub
> >>
> >> 'This is workbook event code.
> >> 'To input this code, right click on the Excel icon on the worksheet
> >> '(or next to the File menu if you maximise your workbooks),
> >> 'select View Code from the menu, and paste the code
> >>
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> "joeeng" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I would like to know how to insert the contents of a particular cell (in
> >> each
> >> > worksheet) into the header/footer for the corresponding worksheet.
> >>
> >>
> >>
>
>
>

Yes

See how I use the font size here
http://www.rondebruin.nl/print.htm#Saved

Check out the VBA help for all formatting codes.
Look for "Formatting Codes for Headers and Footers"

You can use something like this :

.CenterFooter = "&8Page &P & of &N"
.RightFooter = "&8Last Saved : &B" & ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
.LeftFooter = "&8" & ActiveWorkbook.FullName & Chr(10) & "Sheetname : &B" & ActiveSheet.Name




--
Regards Ron de Bruin
http://www.rondebruin.nl


"joeeng" <[email protected]> wrote in message news:[email protected]...
> Can I change the font characteristics in this routine?
>
> "Ron de Bruin" wrote:
>
>> You can loop through the sheets
>>
>> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>> Dim wkSht As Worksheet
>> For Each wkSht In ThisWorkbook.Worksheets
>> wkSht.PageSetup.LeftFooter = wkSht.Range("A1").Value
>> Next wkSht
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "joeeng" <[email protected]> wrote in message news:[email protected]...
>> >I would like this routine to work for all worksheets in the workbook when the
>> > "print entire workbook" selection is made. The routine below only does the
>> > first worksheet footer when I print the entire workbook.
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>> >> With ActiveSheet.PageSetup
>> >> .LeftFooter = Range("A1").Value
>> >> End With
>> >> End Sub
>> >>
>> >> 'This is workbook event code.
>> >> 'To input this code, right click on the Excel icon on the worksheet
>> >> '(or next to the File menu if you maximise your workbooks),
>> >> 'select View Code from the menu, and paste the code
>> >>
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Bob Phillips
>> >>
>> >> "joeeng" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > I would like to know how to insert the contents of a particular cell (in
>> >> each
>> >> > worksheet) into the header/footer for the corresponding worksheet.
>> >>
>> >>
>> >>
>>
>>
>>

Thanks, that worked.

"Ron de Bruin" wrote:

> Yes
>
> See how I use the font size here
> http://www.rondebruin.nl/print.htm#Saved
>
> Check out the VBA help for all formatting codes.
> Look for "Formatting Codes for Headers and Footers"
>
> You can use something like this :
>
> .CenterFooter = "&8Page &P & of &N"
> .RightFooter = "&8Last Saved : &B" & ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
> .LeftFooter = "&8" & ActiveWorkbook.FullName & Chr(10) & "Sheetname : &B" & ActiveSheet.Name
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "joeeng" <[email protected]> wrote in message news:[email protected]...
> > Can I change the font characteristics in this routine?
> >
> > "Ron de Bruin" wrote:
> >
> >> You can loop through the sheets
> >>
> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >> Dim wkSht As Worksheet
> >> For Each wkSht In ThisWorkbook.Worksheets
> >> wkSht.PageSetup.LeftFooter = wkSht.Range("A1").Value
> >> Next wkSht
> >> End Sub
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "joeeng" <[email protected]> wrote in message news:[email protected]...
> >> >I would like this routine to work for all worksheets in the workbook when the
> >> > "print entire workbook" selection is made. The routine below only does the
> >> > first worksheet footer when I print the entire workbook.
> >> >
> >> > "Bob Phillips" wrote:
> >> >
> >> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >> >> With ActiveSheet.PageSetup
> >> >> .LeftFooter = Range("A1").Value
> >> >> End With
> >> >> End Sub
> >> >>
> >> >> 'This is workbook event code.
> >> >> 'To input this code, right click on the Excel icon on the worksheet
> >> >> '(or next to the File menu if you maximise your workbooks),
> >> >> 'select View Code from the menu, and paste the code
> >> >>
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Bob Phillips
> >> >>
> >> >> "joeeng" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > I would like to know how to insert the contents of a particular cell (in
> >> >> each
> >> >> > worksheet) into the header/footer for the corresponding worksheet.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

So I'm using a snippet of code to get the contents of a cell into the header but it's exhibiting some odd behavior. The code I'm using is ...

Sub HeaderFrom_P1()
With ActiveSheet.PageSetup
.RightHeader = "&14&""Arial,Bold""" & Range("P1").Value
End With
End Sub

Obviously, cell P1 contains the text I want in the header. Note that I started with an incarnation that used cell N1. When my worksheet changed, I changed the cell to P1 from N1. Because I had some odd behavior happening - I'll get to that in a minute - I deleted the macro entirely and re-entered it to change the cell to P1. But I'm still getting some odd behavior...

When I run this macro, I check page layout and, as expected, P1 contents is inserted in the header. Then I Print Preview and again, all is well. Then I actually print out the document and the header has suddenly changed from the contents of P1 to N1. It's as if the old macro (that used N1) is lurking in the background waiting until the last second to work its magic. And after printing, I go back and look at the header in Page Setup and the incorrect N1 has, indeed, been inserted into the header.

I'm running Office 365/Excel 2013 on a Windows 7 machine.

Thanks.

Ok I'm definitely not getting this; I did as indicated above and then what? Copied and pasted the code but nothing happens