I have a worksheet that will be printed one page high and 2 or 3 pages wide (varies, based on how many columns get hidden, which varies by scenario). I would like cells A1 through A4 (or wherever it is best to put them) to be repeated at the top of each page. Unfortunately, particularly on the first page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each page?

I thought about a custom header, instead, but it doesn't seem like the header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

You can set the headers/footers up with data from cells..... e.g.

Sub SetHeadingFooters()
With Sheets("Sheet1").PageSetup
.LeftHeader = Range("A1")
.CenterHeader = Range("A2")
.RightHeader = Range("A3")
.LeftFooter = Range("A4")
.CenterFooter = Range("A5")
.RightFooter = Range("A6")
End With
End Sub
--
Cheers
Nigel



"Dean" wrote in message ...
I have a worksheet that will be printed one page high and 2 or 3 pages wide (varies, based on how many columns get hidden, which varies by scenario). I would like cells A1 through A4 (or wherever it is best to put them) to be repeated at the top of each page. Unfortunately, particularly on the first page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each page?

I thought about a custom header, instead, but it doesn't seem like the header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

This looks good, as does Nick's. I will have to choose between them - thank you both!
"Nigel" wrote in message ...
You can set the headers/footers up with data from cells..... e.g.

Sub SetHeadingFooters()
With Sheets("Sheet1").PageSetup
.LeftHeader = Range("A1")
.CenterHeader = Range("A2")
.RightHeader = Range("A3")
.LeftFooter = Range("A4")
.CenterFooter = Range("A5")
.RightFooter = Range("A6")
End With
End Sub
--
Cheers
Nigel



"Dean" wrote in message ...
I have a worksheet that will be printed one page high and 2 or 3 pages wide (varies, based on how many columns get hidden, which varies by scenario). I would like cells A1 through A4 (or wherever it is best to put them) to be repeated at the top of each page. Unfortunately, particularly on the first page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each page?

I thought about a custom header, instead, but it doesn't seem like the header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages wide
(varies, based on how many columns get hidden, which varies by scenario). I
would like cells A1 through A4 (or wherever it is best to put them) to be
repeated at the top of each page. Unfortunately, particularly on the first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

Can you tell me how to get this to be centered from left to right (on each
page)? Also, how do I have it cover multiple worksheets in the same macro,
say "Jack", "Queen", and "Ace". Also, can I make it larger in size?

Thanks much!
Dean

"NickHK" wrote in message
...
Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages
wide
(varies, based on how many columns get hidden, which varies by scenario).
I
would like cells A1 through A4 (or wherever it is best to put them) to be
repeated at the top of each page. Unfortunately, particularly on the
first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

It seems like my replies to this newsgroup, via Outlook Express (OE), seem
to keep disappearing into a black hole so, once again, forgive me if this
shows up twice! BTW, what is the URL to log into this forum directly, in
case I don't trust my OE?

How do I get it to center this heading from left to right across (each)
page. Also, how can I make the letters bigger, maybe even bold fonted?

Also, can I get it to do this same thing on multiple worksheets, or do I
need to make a copy of this macro for each?

Dean

"NickHK" wrote in message
...
Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages
wide
(varies, based on how many columns get hidden, which varies by scenario).
I
would like cells A1 through A4 (or wherever it is best to put them) to be
repeated at the top of each page. Unfortunately, particularly on the
first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

Ok, for a 3rd time, this time with msnews.microsoft.com as my default, I am
going to attempt a reply!

I thank you very much. Can you tell me how to change the font size, maybe
even bold fonted? Also, your command:

<< With Worksheets("WhichOne")

suggests I can program this for multiple worksheet names, yet "which one"
suggests only one. To do more than one, do I use commas between names. And
will that allow me to print only one of the sheets (for a single print job)
or would it somehow insist I print all the sheets listed?

Thanks!
Dean

"NickHK" wrote in message
...
Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages
wide
(varies, based on how many columns get hidden, which varies by scenario).
I
would like cells A1 through A4 (or wherever it is best to put them) to be
repeated at the top of each page. Unfortunately, particularly on the
first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean

One way:

Dim mySheetNames as variant
dim iCtr as long

mysheetnames = array("sheet1", "sheet99", "anothersheetnamehere")

for ictr = lbound(mysheetnames) to ubound(mysheetnames)
with worksheets(mysheetnames(ictr))
'do your stuff
end with
next ictr



Dean wrote:

Ok, for a 3rd time, this time with msnews.microsoft.com as my default, I am
going to attempt a reply!

I thank you very much. Can you tell me how to change the font size, maybe
even bold fonted? Also, your command:

<< With Worksheets("WhichOne")

suggests I can program this for multiple worksheet names, yet "which one"
suggests only one. To do more than one, do I use commas between names. And
will that allow me to print only one of the sheets (for a single print job)
or would it somehow insist I print all the sheets listed?

Thanks!
Dean

"NickHK" wrote in message
...
Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages
wide
(varies, based on how many columns get hidden, which varies by scenario).
I
would like cells A1 through A4 (or wherever it is best to put them) to be
repeated at the top of each page. Unfortunately, particularly on the
first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean



--

Dave Peterson

It didn't quite work for me. Can you tell me what is wrong (see macro
below)? It looks like it bombed at the ,pagesetup command near the very
bottom, i.e., when I clicked debug, it was the

Thanks
Dean

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

Dim mySheetNames As Variant
Dim iCtr As Long

mySheetNames = Array("IS-LA", "BS-LA", "BS-LA-CV")

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(iCtr))
'do your stuff


For Each Cell In Range("a1:a4")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets(mySheetNames)
.PageSetup.CenterHeader = HeaderStr
End With
End With
Next iCtr
End Sub


"Dave Peterson" wrote in message
...
One way:

Dim mySheetNames as variant
dim iCtr as long

mysheetnames = array("sheet1", "sheet99", "anothersheetnamehere")

for ictr = lbound(mysheetnames) to ubound(mysheetnames)
with worksheets(mysheetnames(ictr))
'do your stuff
end with
next ictr



Dean wrote:

Ok, for a 3rd time, this time with msnews.microsoft.com as my default, I
am
going to attempt a reply!

I thank you very much. Can you tell me how to change the font size,
maybe
even bold fonted? Also, your command:

<< With Worksheets("WhichOne")

suggests I can program this for multiple worksheet names, yet "which one"
suggests only one. To do more than one, do I use commas between names.
And
will that allow me to print only one of the sheets (for a single print
job)
or would it somehow insist I print all the sheets listed?

Thanks!
Dean

"NickHK" wrote in message
...
Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages
wide
(varies, based on how many columns get hidden, which varies by
scenario).
I
would like cells A1 through A4 (or wherever it is best to put them) to
be
repeated at the top of each page. Unfortunately, particularly on the
first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of
each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean



--

Dave Peterson

Notice the "with worksheets(mysheetnames(ictr))" line.

When you used it later, you didn't include the (ictr) stuff:
"With Worksheets(mySheetNames)"

But since you're just duplicating that same loop, you could just include that
code in the other loop and be done with it.

And one more thing to watch for...

When you use:
For Each Cell In Range("a1:a4")
it's not using the range that's on each of the sheets. That unqualified range
will just take the data from A1:A4 of the activesheet.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

Dim mySheetNames As Variant
Dim iCtr As Long

mySheetNames = Array("IS-LA", "BS-LA", "BS-LA-CV")

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(iCtr))
'do your stuff
For Each Cell In .Range("a1:a4")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next Cell
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)
.PageSetup.CenterHeader = HeaderStr
End With
Next iCtr
End Sub

Notice the dot in front of .range("a1:A4"). That tells excel to use the object
in the previous With statement. In this case, the
worksheets(mysheetnames(ictr)) worksheet.

Dean wrote:

It didn't quite work for me. Can you tell me what is wrong (see macro
below)? It looks like it bombed at the ,pagesetup command near the very
bottom, i.e., when I clicked debug, it was the

Thanks
Dean

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

Dim mySheetNames As Variant
Dim iCtr As Long

mySheetNames = Array("IS-LA", "BS-LA", "BS-LA-CV")

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(iCtr))
'do your stuff

For Each Cell In Range("a1:a4")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets(mySheetNames)
.PageSetup.CenterHeader = HeaderStr
End With
End With
Next iCtr
End Sub

"Dave Peterson" wrote in message
...
One way:

Dim mySheetNames as variant
dim iCtr as long

mysheetnames = array("sheet1", "sheet99", "anothersheetnamehere")

for ictr = lbound(mysheetnames) to ubound(mysheetnames)
with worksheets(mysheetnames(ictr))
'do your stuff
end with
next ictr



Dean wrote:

Ok, for a 3rd time, this time with msnews.microsoft.com as my default, I
am
going to attempt a reply!

I thank you very much. Can you tell me how to change the font size,
maybe
even bold fonted? Also, your command:

<< With Worksheets("WhichOne")

suggests I can program this for multiple worksheet names, yet "which one"
suggests only one. To do more than one, do I use commas between names.
And
will that allow me to print only one of the sheets (for a single print
job)
or would it somehow insist I print all the sheets listed?

Thanks!
Dean

"NickHK" wrote in message
...
Dean,
How about :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Cell As Range
Dim HeaderStr As String

For Each Cell In Range("HeaderCells")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)

With Worksheets("WhichOne")
.PageSetup.CenterHeader = HeaderStr
End With

End Sub

NickHK

"Dean" wrote in message
...
I have a worksheet that will be printed one page high and 2 or 3 pages
wide
(varies, based on how many columns get hidden, which varies by
scenario).
I
would like cells A1 through A4 (or wherever it is best to put them) to
be
repeated at the top of each page. Unfortunately, particularly on the
first
page, some columns will be hidden and this is a variable.

How can I make these four cells print out at the top (and center) of
each
page?

I thought about a custom header, instead, but it doesn't seem like the
header can be based on a cell contents, which seems to be what I need.

Any ideas?

Thanks much,
Dean



--

Dave Peterson

--

Dave Peterson