Print contents of a cell in Header

[PurrSent]

Hi all

I'm still having difficulty finding out how (if) I can have the contents of one particular cell, ie C3 on each sheet, printed in a Header, alongside the Sheet Name.

I would like to be able to type in the year the spreadsheet applies to, ie 2019 for this year, and then when the worksheet is printed out, the tab Sheet Name plus '2019' from C3 (or another year ie 2020 etc) on each sheet, as a Header.

Is there a formula perhaps, or would I need macro/vba? If the latter, I would appreciate some help with the coding, please.

I've previously asked this question but I don't feel I explained very well and didn't get an answer so I'm trying again, hoping I can find a solution to this (previous post - https://www.mrexcel.com/forum/excel...pls-excel-2010-a-post5213814.html#post5213814 )

Many thanks for any help / advice etc

 

[Yongle]

Re: ? Poss to Print contents of a cell in Header

Excel itself does not support adding cell values to headers and footers
But it is easy with VBA

Add to a module and run before printing to refresh values

Sub Header()
    With ActiveSheet
        .PageSetup.LeftHeader = .Name & vbCr & .Range("C3").Value & vbCr & "Printed " & Date
    End With
End Sub

 

[PurrSent]

Re: ? Poss to Print contents of a cell in Header

Hi Yongle

Many thanks for this VBA. I'm pleased to say this macro works, so this is great. I forgot to mention that the Workbook would be protected so I've included the relevant code, which also works.

However, the Sheet Name, Year (content of cell C3) and date printed are all on separate lines. Is there a way for the code to be amended to put all this text on the same line, please?

Many thanks

 

[Yongle]

Re: ? Poss to Print contents of a cell in Header

Replace
vbCr
with
" "
(quote marks with space(s))

 

[PurrSent]

Re: ? Poss to Print contents of a cell in Header

Bless you,
Many thanks
I'll give that a try