Printing Custom Header

[Guest]

I would like to print information that is entered deep in the spreadsheet
(i.e. A200, E205) as a header. I can't seem to find a way to define a range
of cells as a custom header.

 

[Dave Peterson]

You'll need a little macro.

Record a macro when you change the header the way you want.

Then instead of burying the value in the code, you can pick up the value from a
cell on that sheet.

My recorded code looked like:

With ActiveSheet.PageSetup
.LeftHeader = "asdfadsfasdf"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&D &T"

I changed it to:

With Worksheets("sheet1").PageSetup
.LeftHeader = .Parent.Range("a200").Value
End With

(I tossed all the things I didn't want to touch, too!)

But now to make it update right before I hit the print button, the code has to
be placed behind the ThisWorkbook module in the correct event procedure
(_beforeprint).

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1").PageSetup
.LeftHeader = .Parent.Range("a200").Value
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm