Ok, so here's my problem -

I'm trying to customize a Header to be displayed in different workbooks. I want the Header to contain the name of the Worksheet as well as the current year. The header will read this way:

Month of August 2010

For the purposes of my code, I have the following code in my macro where I have manually typed in the year:

With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&12Month of &A 2010"
.CenterHeader = ""
.RightHeader = _
"
.RightHeader = _
""Arial,Bold""Report Date and Time:&""Arial,Regular"" &D &T"
.LeftFooter = "&""Arial,Bold""Page &P of &N"
End With

However, I'd like to make the year dynamic and one way I have thought to do that is by take a cell from the Summary Worksheet of my workbook and take the Left 4 characters from cell A1, and return that value to the Left Header to concatenate the above sequence with the returned Year value:

.LeftHeader = "&""Arial,Bold""&12Month of &A" & returnValue

I get lost in the code when trying to set the Dim returnValue as String and referencing the Summary!A19 code, taking the Left(Summary!A19,4) and returning that value.

Any ideas?

Thanks.

Maybe this:


.LeftHeader = "&""Arial,Bold""&12Month of &[A] " & Left(Sheets("Summary").Range("A19"), 4)


Or even:

.LeftHeader = "&""Arial,Bold""&12Month of &[A] " & Year(Date)