Linking cells to Headers and Footers

[Gregory]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

But that's not what your code did.

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:

Should have been:

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.

 

[Dave Peterson]

It didn't work for me.

This is a guess.

I bet you created a test workbook with a few worksheets. And put something in
A1 of each sheet.

Then you ran the code.

And when you did print preview, you saw the stuff that was in A1 at the top of
each sheet--except for a single page--and that had it twice.

(That's what I did.)

The sheet where it showed up twice had the header and the data (the print range
included A1).

The other sheets didn't have any headers and only showed A1 as part of the print
range.

If you add a fill color to A1 of each sheet, it may make it easier to see.

Sorry to follow up my own post so quickly.

Clearly, ActiveSheet is not changed in my loop.

Yet, with my code the header for each WKS is tied to each WKS, not
ActiveSheet.

I am confused. Is this a bug?

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?

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.

 

[Dave Peterson]

But we worked it out in the end--and that's what matters!

You are spot-on, Dave. I should have noticed.

Actually it was a little tricker to see the error of my ways than you
describe.

I started with one worksheet (my default configuration). Run the code,
looks fine.

Add a second worksheet to "make sure" it works on multiple sheets, run
code (you guessed it, with the new sheet active) -- looks fine. Add a
third sheet later, "just to be really sure" -- you get the idea.

So my testing was bunk.

I am now making omelettes with the egg on my face (^: There's plenty to
go around -- everyone is invited!

It didn't work for me.

This is a guess.

I bet you created a test workbook with a few worksheets. And put something in
A1 of each sheet.

Then you ran the code.

And when you did print preview, you saw the stuff that was in A1 at the top of
each sheet--except for a single page--and that had it twice.

(That's what I did.)

The sheet where it showed up twice had the header and the data (the print range
included A1).

The other sheets didn't have any headers and only showed A1 as part of the print
range.

If you add a fill color to A1 of each sheet, it may make it easier to see.

Sorry to follow up my own post so quickly.

Clearly, ActiveSheet is not changed in my loop.

Yet, with my code the header for each WKS is tied to each WKS, not
ActiveSheet.

I am confused. Is this a bug?

smartin wrote:
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.