ExcelBanter - change header for each worksheet

Tue, 29 Jan 2008 11:51:03 -0800 from cartmr
:
How do I pull content from adjacent cells to print as header
information in multiple sheets?

By "header information", do you mean the headers that are normally
edited with View | Header and Footer?

If so, as far as I understand things you need a macro. Here's the one
I use. You can modify it for the paticular cells you want. I
recommend named ranges like my "course" and "semester", rather than
cell references, just because it keeps the macro shorter.

Sub Workbook_Open()
Dim ws As Worksheet
Dim thisYear As Integer
thisYear = Evaluate("Year(FirstClass)")
For Each ws In Worksheets
ws.PageSetup.LeftHeader = Worksheets("Rules").Range("course").Value _
& "/" & Worksheets("Rules").Range("section").Value
ws.PageSetup.RightHeader = Worksheets("Rules").Range("semester").Value & " " & thisYear
Next
ActiveWorkbook.Saved = True
End Sub

This macro runs automatically when the workbook opens. The last
statement, ActiveWorkbook.Saved = True, ensures that I don't get the
"do you want to save?" prompt if I open the workbook but don't
actually change anything before closing it.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/