Dave, either I disagree, or misunderstand.

Using Excel 2003 create 2+ worksheets, each with a different value in
A1, and run my code in a module. Print [Preview]. QED. (Works for me.)

Having said that, I confess I am now a little fuzzy /why/ this works,
and in retrospect I think indeed it should not work for the reasons you
cite. And yet, it works.

I will admit though, my style may be sloppy. Apparently I have engaged
some kind of default property/event that is not intuitive (or should not
be intuitive, even though my sloppy brain conceived it). To wit, it
seems I assumed (or discovered)

With Each WKS ...

somehow makes WKS the ActiveSheet in each pass?

What do you think?



Dave Peterson wrote:
But that's not what your code did.

Sub MakeHeaders()
Dim WKS As Worksheet

Application.ScreenUpdating = False
With ActiveSheet.PageSetup
For Each WKS In ActiveWorkbook.Worksheets
.LeftHeader = Range("A1").Text
Next WKS
End With
Application.ScreenUpdating = True
End Sub

The .leftheader refers to the activesheet.pagesetup. And the unqualified
Range("A1") refers to the activesheet.

Neither of those things change.

So you're plopping the text in A1 of the activesheet into the activesheet's
header. And you're doing it as many times as there are worksheets in the
activeworkbook.

That's why I suggested dropping the "with/end with" stuff and qualifying the
ranges.

But my code did have a typo in it:

wks.LeftHeader = wks.Range("A1").Text
'or use the text from A1 of the Master sheet
wks.leftheader = worksheets("Master").range("A1").text

Should have been:

wks.pagesetup.LeftHeader = wks.Range("A1").Text
'or use the text from A1 of the Master sheet
wks.pagesetup.leftheader = worksheets("Master").range("A1").text

The second line (with the "master" reference) was because I read the original
post as wanting to take something from that sheet and put it on each worksheet's
header.




smartin wrote:
Eh? Thought that's what I said. This would have been clearer:

"...create a header /for each worksheet/ with whatever is in cell A1 /of
that worksheet/"

I thought that's what the OP wanted, but I might have misunderstood.

Regards, Smartin.

Dave Peterson wrote:
Actually, this places the text of what's in A1 of the activesheet into the
activesheet's print header. But it does it for each worksheet in that workbook.

Sub MakeHeaders()
Dim WKS As Worksheet

Application.ScreenUpdating = False
For Each WKS In ActiveWorkbook.Worksheets
'use the text in A1 of each sheet
wks.LeftHeader = wks.Range("A1").Text
'or use the text from A1 of the Master sheet
wks.leftheader = worksheets("Master").range("A1").text
Next WKS
Application.ScreenUpdating = True
End Sub

smartin wrote:
Gregory wrote:
I would like to have a worksheet (Master) which has the information
that I would like to have put into various headers in different
worksheets. I'd like to link the header elements of the different
worksheets to this Master so that when I want to change the name of a
company, exhibit number, etc., it automatically changes the header.
Is this possible?

Thanks in advance, Gregory
You can make headers & c. dynamic using VBA. This code will create a
header with whatever is in cell A1 of each worksheet:

Sub MakeHeaders()
Dim WKS As Worksheet

Application.ScreenUpdating = False
With ActiveSheet.PageSetup
For Each WKS In ActiveWorkbook.Worksheets
.LeftHeader = Range("A1").Text
Next WKS
End With
Application.ScreenUpdating = True
End Sub

To extend this, record a macro as you manually create an arbitrary
header. The code it creates will reveal a plethora of other page setup
options you can tweak in code.

To automate the process of updating the headers, place something in the
workbook before_print event that calls MakeHeaders.