Can custom headers be linked to a cell in the worksheet?

T

Texas_Toast

This was a feature in 123 where header or footer information could be
retrieved from a cell in the worksheet. Is there anything similar in Excel?
 
D

Dave Peterson

You could use a macro...

For instance, this code goes behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("sheet1")
.PageSetup.LeftHeader = .Range("a1").Text
End With
End Sub
 
P

Pam in California

I'm trying to enter this marco, and need some clarification as I am having
some trouble with with macro.
With Worksheets("sheet 1"), sheet 1 would be the worksheet that contains
the information, and the range "a1" is the cell, correct? My worksheet name
is Expense Worksheet. Do I need to change the worksheet name to
Expense_Worksheet?

I would like the text in cell a1, which is my e7, to be in the header on all
7 of my worksheets - is this possible?

I hope this makes sense. Thanks for your help.
-pam
 
D

Dave Peterson

You would change this:

With Worksheets("sheet1")
to
With Worksheets("Expense Worksheet")
(It has to match exactly.)

(It really is named "expense worksheet"??)
 
P

Pam in California

So my macro would look like this:
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets ("Expense_Worksheet")
.PageSetup.LeftHeader = .Range("e7").Text
End With
End Sub

Sorry to be dense on this. The header isn't pulling the information from e7
so I think I haven't done this properly.
-pam
 
D

Dave Peterson

Does that worksheet name have a space between words or an underscore?

Expense_Worksheet
or
Expense Worksheet

You have to match it exactly the way you see it on the tab itself.


So my macro would look like this:
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets ("Expense_Worksheet")
.PageSetup.LeftHeader = .Range("e7").Text
End With
End Sub

Sorry to be dense on this. The header isn't pulling the information from e7
so I think I haven't done this properly.
-pam
Click to expand...
 
P

Pam in California

I was able to get the macro to work on the first worksheet. What wording do
I use to have it work on subsequent worksheets. Do I list each worksheet
individually or change the .PageSetup.LeftHeader =.Range("Expense_Worksheet
e7").text or something like that.

thanks,
-pam
 
P

Pam in California

One more question - the font prints small. What's the line I would use to
print times roman 12 pt.

Thanks again for your help.
-pam
 
D

Dave Peterson

It depends on what you want to do.

Do you want every worksheet in the workbook to have that header that from
Expense_worksheet (with the underscore)?

Do you want just some to have it?

Do you want to use E7 from each of the worksheets in that worksheet's header?

I'm guessing that not all get the header, but when it does, it comes from the
Expense_worksheet.

If I'm close, then one way:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim wCtr As Long
Dim WksNames As Variant

WksNames = Array("Expense_Worksheet", "some other sheetname", "sheet 99")

For wCtr = LBound(WksNames) To UBound(WksNames)
Worksheets(WksNames(wCtr)).PageSetup.LeftHeader _
= Worksheets("expense_worksheet").Range("e7").Text
Next wCtr

End Sub

Just keep including the names you want in that array() stuff.

But remember to match them exactly.


I was able to get the macro to work on the first worksheet. What wording do
I use to have it work on subsequent worksheets. Do I list each worksheet
individually or change the .PageSetup.LeftHeader =.Range("Expense_Worksheet
e7").text or something like that.

thanks,
-pam
Click to expand...
 
D

Dave Peterson

Record a macro when you change the font to what you like in a header.

..PageSetup.LeftHeader = .Range("e7").Text
becomes:
..PageSetup.LeftHeader _
= "&""Times New Roman,Regular""&12 " & .range("e7").text

or maybe (after the other post):

For wCtr = LBound(WksNames) To UBound(WksNames)
Worksheets(WksNames(wCtr)).PageSetup.LeftHeader _
= "&""Times New Roman,Regular""&12 " _
& Worksheets("Expense_Worksheet").Range("e7").Text
Next wCtr
One more question - the font prints small. What's the line I would use to
print times roman 12 pt.

Thanks again for your help.
-pam
Click to expand...
 
Top