Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

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

Print Rows
 

Very nice! I have attached the new macro, in case what I edited came out
differently than what you wrote, because it is still bombing out at the same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

Print Rows
 

I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited came out
differently than what you wrote, because it is still bombing out at the same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Print Rows
 

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited came out
differently than what you wrote, because it is still bombing out at the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Print Rows
 

Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same with
Footers.)

If that's not the problem, then maybe it's something in one of the cells that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want to save some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited came out
differently than what you wrote, because it is still bombing out at the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

Print Rows
 

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same with
Footers.)

If that's not the problem, then maybe it's something in one of the cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want to save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited came
out
differently than what you wrote, because it is still bombing out at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

Print Rows
 

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply the
macro to only whatever worksheet I was trying to print and, as far as I can
tell (because it does seem to print it, even perhaps with no problems, after
I hit end, after the macro crashes), it just prints the one worksheet. So I
know which sheet is causing the problem. By printing the other two sheets,
I can tell you that the problem occurs on each sheet. Or do you think I am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I should try
to minimize my print previews?

Thanks, Dave!
Dean


"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same with
Footers.)

If that's not the problem, then maybe it's something in one of the cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want to save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited came
out
differently than what you wrote, because it is still bombing out at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

Print Rows
 

About the print previews: I was just suggesting that as a way to test the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is not what the
code does. The code processes all the sheets that have names in that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I think that
the next step is to look at the stuff in A1:A4 of that sheet. And then to look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply the
macro to only whatever worksheet I was trying to print and, as far as I can
tell (because it does seem to print it, even perhaps with no problems, after
I hit end, after the macro crashes), it just prints the one worksheet. So I
know which sheet is causing the problem. By printing the other two sheets,
I can tell you that the problem occurs on each sheet. Or do you think I am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I should try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same with
Footers.)

If that's not the problem, then maybe it's something in one of the cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want to save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited came
out
differently than what you wrote, because it is still bombing out at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

Indeed, it looks like the headings may be too long after all, but that seems
to be happening because each time the macro is invoked, it adds yet another
heading to the one it established the last time the macro was run (it may
max out at twice). Also, the headings that are already there seem to be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is doing.

Back to the original intent. I wanted it to only do this for only the
active sheet I am on, when I hit the print icon. Kindly edit the macro as
needed.

Lastly, back when I added the macro, I had some huge font sizes and a small,
e.g. 25% EXCEL resolution. I don't know if this is why but the headings
printed via this macro are microscopic, something I don't want. Can you
edit the macro to make them bigger?

Thanks again, Dave!
Dean


"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to test the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is not
what the
code does. The code processes all the sheets that have names in that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I think
that
the next step is to look at the stuff in A1:A4 of that sheet. And then to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply the
macro to only whatever worksheet I was trying to print and, as far as I
can
tell (because it does seem to print it, even perhaps with no problems,
after
I hit end, after the macro crashes), it just prints the one worksheet.
So I
know which sheet is causing the problem. By printing the other two
sheets,
I can tell you that the problem occurs on each sheet. Or do you think I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same
with
Footers.)

If that's not the problem, then maybe it's something in one of the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited
came
out
differently than what you wrote, because it is still bombing out at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that need them.
There's nothing in this event (workbook_beforeprint) that tells you what sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but that seems
to be happening because each time the macro is invoked, it adds yet another
heading to the one it established the last time the macro was run (it may
max out at twice). Also, the headings that are already there seem to be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is doing.

Back to the original intent. I wanted it to only do this for only the
active sheet I am on, when I hit the print icon. Kindly edit the macro as
needed.

Lastly, back when I added the macro, I had some huge font sizes and a small,
e.g. 25% EXCEL resolution. I don't know if this is why but the headings
printed via this macro are microscopic, something I don't want. Can you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to test the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is not
what the
code does. The code processes all the sheets that have names in that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I think
that
the next step is to look at the stuff in A1:A4 of that sheet. And then to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply the
macro to only whatever worksheet I was trying to print and, as far as I
can
tell (because it does seem to print it, even perhaps with no problems,
after
I hit end, after the macro crashes), it just prints the one worksheet.
So I
know which sheet is causing the problem. By printing the other two
sheets,
I can tell you that the problem occurs on each sheet. Or do you think I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same
with
Footers.)

If that's not the problem, then maybe it's something in one of the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited
came
out
differently than what you wrote, because it is still bombing out at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20 other
variations (all to no avail), but since I did it (recorded a macro that
enlarged the font size) on text that was already within the text box, it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean

"Dave Peterson" wrote in message
...
Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that need
them.
There's nothing in this event (workbook_beforeprint) that tells you what
sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but that
seems
to be happening because each time the macro is invoked, it adds yet
another
heading to the one it established the last time the macro was run (it may
max out at twice). Also, the headings that are already there seem to be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is doing.

Back to the original intent. I wanted it to only do this for only the
active sheet I am on, when I hit the print icon. Kindly edit the macro
as
needed.

Lastly, back when I added the macro, I had some huge font sizes and a
small,
e.g. 25% EXCEL resolution. I don't know if this is why but the headings
printed via this macro are microscopic, something I don't want. Can you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to test
the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is not
what the
code does. The code processes all the sheets that have names in that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I
think
that
the next step is to look at the stuff in A1:A4 of that sheet. And then
to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I
doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply
the
macro to only whatever worksheet I was trying to print and, as far as
I
can
tell (because it does seem to print it, even perhaps with no problems,
after
I hit end, after the macro crashes), it just prints the one worksheet.
So I
know which sheet is causing the problem. By printing the other two
sheets,
I can tell you that the problem occurs on each sheet. Or do you think
I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I
should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same
with
Footers.)

If that's not the problem, then maybe it's something in one of the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want
to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited
came
out
differently than what you wrote, because it is still bombing out
at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the
Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

I recorded a macro when I change the center header to "asdf" and used a font
size of 72.

This was the important part of that recorded macro:

With ActiveSheet.PageSetup
.CenterHeader = "&72asdf"
End With

So my code changed to:

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)
HeaderStr = "&72" '<--start with that font size string.
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

72 point seems a little large to me, though.

Dean wrote:

HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20 other
variations (all to no avail), but since I did it (recorded a macro that
enlarged the font size) on text that was already within the text box, it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean

"Dave Peterson" wrote in message
...
Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that need
them.
There's nothing in this event (workbook_beforeprint) that tells you what
sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but that
seems
to be happening because each time the macro is invoked, it adds yet
another
heading to the one it established the last time the macro was run (it may
max out at twice). Also, the headings that are already there seem to be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is doing.

Back to the original intent. I wanted it to only do this for only the
active sheet I am on, when I hit the print icon. Kindly edit the macro
as
needed.

Lastly, back when I added the macro, I had some huge font sizes and a
small,
e.g. 25% EXCEL resolution. I don't know if this is why but the headings
printed via this macro are microscopic, something I don't want. Can you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to test
the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is not
what the
code does. The code processes all the sheets that have names in that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I
think
that
the next step is to look at the stuff in A1:A4 of that sheet. And then
to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I
doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply
the
macro to only whatever worksheet I was trying to print and, as far as
I
can
tell (because it does seem to print it, even perhaps with no problems,
after
I hit end, after the macro crashes), it just prints the one worksheet.
So I
know which sheet is causing the problem. By printing the other two
sheets,
I can tell you that the problem occurs on each sheet. Or do you think
I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I
should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255. (Same
with
Footers.)

If that's not the problem, then maybe it's something in one of the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may want
to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I edited
came
out
differently than what you wrote, because it is still bombing out
at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the
Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

I could have sworn I tried some variant like that, but it did not work.
This does. Thanks!

Regarding the size of 72, I've buried myself in a hole by accepting editing
of my file by a client who modified it to have fonts of 40. I'm not sure
why, but the header font size adjustment could be smaller if it were 2 pages
wide by 1 page high, as I have set it. But for some reason, it keeps
replacing that setting with a computed % of normal size and when it does
that, it's usually, like, 10%, which renders the headings microscopic -
SHOULD IT BE DOING THIS? This is why I need to make the header so big to
compensate. Also, it is set to have as many as 200 columns, though
typically it only has about 40. I run a macro to hide all the unused
columns. I guess I should reset the width AFTER I delete the empty columns.

I think I need to reject that large font size, I think. The problem is that
the exhibit is too short compared to its width, and the client wants it
taller. Increasing the font size seems to help (though I'm not sure why),
but the price I'm paying for this huge font size is too dear.

Are there any obvious ways to adjust for an exhibit that is too short
compared to its width, other than leaving empty rows in between (or
increasing the row heights)? We really don't want it to stretch over more
than 2, maybe, 3 pages wide, if there are only about 40 columns.

Dean


"Dave Peterson" wrote in message
...
I recorded a macro when I change the center header to "asdf" and used a
font
size of 72.

This was the important part of that recorded macro:

With ActiveSheet.PageSetup
.CenterHeader = "&72asdf"
End With

So my code changed to:

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)
HeaderStr = "&72" '<--start with that font size string.
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

72 point seems a little large to me, though.

Dean wrote:

HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20
other
variations (all to no avail), but since I did it (recorded a macro that
enlarged the font size) on text that was already within the text box,
it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean

"Dave Peterson" wrote in message
...
Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that need
them.
There's nothing in this event (workbook_beforeprint) that tells you
what
sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the
code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but that
seems
to be happening because each time the macro is invoked, it adds yet
another
heading to the one it established the last time the macro was run (it
may
max out at twice). Also, the headings that are already there seem to
be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is doing.

Back to the original intent. I wanted it to only do this for only the
active sheet I am on, when I hit the print icon. Kindly edit the
macro
as
needed.

Lastly, back when I added the macro, I had some huge font sizes and a
small,
e.g. 25% EXCEL resolution. I don't know if this is why but the
headings
printed via this macro are microscopic, something I don't want. Can
you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to
test
the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is
not
what the
code does. The code processes all the sheets that have names in
that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I
think
that
the next step is to look at the stuff in A1:A4 of that sheet. And
then
to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I
doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply
the
macro to only whatever worksheet I was trying to print and, as far
as
I
can
tell (because it does seem to print it, even perhaps with no
problems,
after
I hit end, after the macro crashes), it just prints the one
worksheet.
So I
know which sheet is causing the problem. By printing the other two
sheets,
I can tell you that the problem occurs on each sheet. Or do you
think
I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I
should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255.
(Same
with
Footers.)

If that's not the problem, then maybe it's something in one of
the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may
want
to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I
edited
came
out
differently than what you wrote, because it is still bombing
out
at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the
Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

I don't have any guess why the page settings are changing after you've changed
them (you did save your changes I'm guessing???).

I've always set the page layout the way I want it--either based on pages wide or
pages tall. And I leave the one I don't care about empty (not 0, not anything).

But I'm not sure that helps you or your client.

Dean wrote:

I could have sworn I tried some variant like that, but it did not work.
This does. Thanks!

Regarding the size of 72, I've buried myself in a hole by accepting editing
of my file by a client who modified it to have fonts of 40. I'm not sure
why, but the header font size adjustment could be smaller if it were 2 pages
wide by 1 page high, as I have set it. But for some reason, it keeps
replacing that setting with a computed % of normal size and when it does
that, it's usually, like, 10%, which renders the headings microscopic -
SHOULD IT BE DOING THIS? This is why I need to make the header so big to
compensate. Also, it is set to have as many as 200 columns, though
typically it only has about 40. I run a macro to hide all the unused
columns. I guess I should reset the width AFTER I delete the empty columns.

I think I need to reject that large font size, I think. The problem is that
the exhibit is too short compared to its width, and the client wants it
taller. Increasing the font size seems to help (though I'm not sure why),
but the price I'm paying for this huge font size is too dear.

Are there any obvious ways to adjust for an exhibit that is too short
compared to its width, other than leaving empty rows in between (or
increasing the row heights)? We really don't want it to stretch over more
than 2, maybe, 3 pages wide, if there are only about 40 columns.

Dean

"Dave Peterson" wrote in message
...
I recorded a macro when I change the center header to "asdf" and used a
font
size of 72.

This was the important part of that recorded macro:

With ActiveSheet.PageSetup
.CenterHeader = "&72asdf"
End With

So my code changed to:

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)
HeaderStr = "&72" '<--start with that font size string.
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

72 point seems a little large to me, though.

Dean wrote:

HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20
other
variations (all to no avail), but since I did it (recorded a macro that
enlarged the font size) on text that was already within the text box,
it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean

"Dave Peterson" wrote in message
...
Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that need
them.
There's nothing in this event (workbook_beforeprint) that tells you
what
sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the
code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but that
seems
to be happening because each time the macro is invoked, it adds yet
another
heading to the one it established the last time the macro was run (it
may
max out at twice). Also, the headings that are already there seem to
be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is doing.

Back to the original intent. I wanted it to only do this for only the
active sheet I am on, when I hit the print icon. Kindly edit the
macro
as
needed.

Lastly, back when I added the macro, I had some huge font sizes and a
small,
e.g. 25% EXCEL resolution. I don't know if this is why but the
headings
printed via this macro are microscopic, something I don't want. Can
you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to
test
the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing is
not
what the
code does. The code processes all the sheets that have names in
that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble, I
think
that
the next step is to look at the stuff in A1:A4 of that sheet. And
then
to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try again.

We're only talking about 100 characters or less in the header, so I
doubt
that's the problem

I am confused by your debugging suggestion. My intent was to apply
the
macro to only whatever worksheet I was trying to print and, as far
as
I
can
tell (because it does seem to print it, even perhaps with no
problems,
after
I hit end, after the macro crashes), it just prints the one
worksheet.
So I
know which sheet is causing the problem. By printing the other two
sheets,
I can tell you that the problem occurs on each sheet. Or do you
think
I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that I
should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and footers.

IIRC, the total number of characters in all 3 Headers is 255.
(Same
with
Footers.)

If that's not the problem, then maybe it's something in one of
the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may
want
to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I
edited
came
out
differently than what you wrote, because it is still bombing
out
at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the
Centerheader
property
of the PageSetup class. Any idea what the problem could be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

I'm starting to think that the # of pages wide by tall is not the real
problem.

I think I have discovered the underlying large font problem. First of all,
at least 15% of the page is taken up by a wide column that is repeated at
the left - I may try to wrap it to counter that problem. Second of all, if
they want bigger numerical characters (most important viewer is an 87 yr old
chairman) AND proportionality, the column width needs to be wider, wide
enough to accommodate the longest single text word (assuming I use wrap for
multiple words). Lastly, we are trying to fit too many columns on a page.
Apparently, a larger font, coupled with a smaller font for text perhaps,
allows us to get more columns on the page. I'm not sure why that is, but it
seems to be true. This means headers and footer needs to be set to these
big fonts, too, unfortunately.

This can all be allieved substantially by putting fewer columns per page,
and hence more pages. We'll see if they prefer that option.

Thanks!
Dean

"Dave Peterson" wrote in message
...
I don't have any guess why the page settings are changing after you've
changed
them (you did save your changes I'm guessing???).

I've always set the page layout the way I want it--either based on pages
wide or
pages tall. And I leave the one I don't care about empty (not 0, not
anything).

But I'm not sure that helps you or your client.

Dean wrote:

I could have sworn I tried some variant like that, but it did not work.
This does. Thanks!

Regarding the size of 72, I've buried myself in a hole by accepting
editing
of my file by a client who modified it to have fonts of 40. I'm not sure
why, but the header font size adjustment could be smaller if it were 2
pages
wide by 1 page high, as I have set it. But for some reason, it keeps
replacing that setting with a computed % of normal size and when it does
that, it's usually, like, 10%, which renders the headings microscopic -
SHOULD IT BE DOING THIS? This is why I need to make the header so big to
compensate. Also, it is set to have as many as 200 columns, though
typically it only has about 40. I run a macro to hide all the unused
columns. I guess I should reset the width AFTER I delete the empty
columns.

I think I need to reject that large font size, I think. The problem is
that
the exhibit is too short compared to its width, and the client wants it
taller. Increasing the font size seems to help (though I'm not sure
why),
but the price I'm paying for this huge font size is too dear.

Are there any obvious ways to adjust for an exhibit that is too short
compared to its width, other than leaving empty rows in between (or
increasing the row heights)? We really don't want it to stretch over
more
than 2, maybe, 3 pages wide, if there are only about 40 columns.

Dean

"Dave Peterson" wrote in message
...
I recorded a macro when I change the center header to "asdf" and used a
font
size of 72.

This was the important part of that recorded macro:

With ActiveSheet.PageSetup
.CenterHeader = "&72asdf"
End With

So my code changed to:

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)
HeaderStr = "&72" '<--start with that font size string.
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

72 point seems a little large to me, though.

Dean wrote:

HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20
other
variations (all to no avail), but since I did it (recorded a macro
that
enlarged the font size) on text that was already within the text box,
it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean

"Dave Peterson" wrote in message
...
Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr
variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that
need
them.
There's nothing in this event (workbook_beforeprint) that tells you
what
sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the
code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but
that
seems
to be happening because each time the macro is invoked, it adds yet
another
heading to the one it established the last time the macro was run
(it
may
max out at twice). Also, the headings that are already there seem
to
be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is
doing.

Back to the original intent. I wanted it to only do this for only
the
active sheet I am on, when I hit the print icon. Kindly edit the
macro
as
needed.

Lastly, back when I added the macro, I had some huge font sizes and
a
small,
e.g. 25% EXCEL resolution. I don't know if this is why but the
headings
printed via this macro are microscopic, something I don't want.
Can
you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to
test
the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing
is
not
what the
code does. The code processes all the sheets that have names in
that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble,
I
think
that
the next step is to look at the stuff in A1:A4 of that sheet.
And
then
to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try
again.

We're only talking about 100 characters or less in the header,
so I
doubt
that's the problem

I am confused by your debugging suggestion. My intent was to
apply
the
macro to only whatever worksheet I was trying to print and, as
far
as
I
can
tell (because it does seem to print it, even perhaps with no
problems,
after
I hit end, after the macro crashes), it just prints the one
worksheet.
So I
know which sheet is causing the problem. By printing the other
two
sheets,
I can tell you that the problem occurs on each sheet. Or do you
think
I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that
I
should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and
footers.

IIRC, the total number of characters in all 3 Headers is 255.
(Same
with
Footers.)

If that's not the problem, then maybe it's something in one of
the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may
want
to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I
edited
came
out
differently than what you wrote, because it is still
bombing
out
at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the
Centerheader
property
of the PageSetup class. Any idea what the problem could
be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in
message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Print Rows
 

Good luck.



Dean wrote:

I'm starting to think that the # of pages wide by tall is not the real
problem.

I think I have discovered the underlying large font problem. First of all,
at least 15% of the page is taken up by a wide column that is repeated at
the left - I may try to wrap it to counter that problem. Second of all, if
they want bigger numerical characters (most important viewer is an 87 yr old
chairman) AND proportionality, the column width needs to be wider, wide
enough to accommodate the longest single text word (assuming I use wrap for
multiple words). Lastly, we are trying to fit too many columns on a page.
Apparently, a larger font, coupled with a smaller font for text perhaps,
allows us to get more columns on the page. I'm not sure why that is, but it
seems to be true. This means headers and footer needs to be set to these
big fonts, too, unfortunately.

This can all be allieved substantially by putting fewer columns per page,
and hence more pages. We'll see if they prefer that option.

Thanks!
Dean

"Dave Peterson" wrote in message
...
I don't have any guess why the page settings are changing after you've
changed
them (you did save your changes I'm guessing???).

I've always set the page layout the way I want it--either based on pages
wide or
pages tall. And I leave the one I don't care about empty (not 0, not
anything).

But I'm not sure that helps you or your client.

Dean wrote:

I could have sworn I tried some variant like that, but it did not work.
This does. Thanks!

Regarding the size of 72, I've buried myself in a hole by accepting
editing
of my file by a client who modified it to have fonts of 40. I'm not sure
why, but the header font size adjustment could be smaller if it were 2
pages
wide by 1 page high, as I have set it. But for some reason, it keeps
replacing that setting with a computed % of normal size and when it does
that, it's usually, like, 10%, which renders the headings microscopic -
SHOULD IT BE DOING THIS? This is why I need to make the header so big to
compensate. Also, it is set to have as many as 200 columns, though
typically it only has about 40. I run a macro to hide all the unused
columns. I guess I should reset the width AFTER I delete the empty
columns.

I think I need to reject that large font size, I think. The problem is
that
the exhibit is too short compared to its width, and the client wants it
taller. Increasing the font size seems to help (though I'm not sure
why),
but the price I'm paying for this huge font size is too dear.

Are there any obvious ways to adjust for an exhibit that is too short
compared to its width, other than leaving empty rows in between (or
increasing the row heights)? We really don't want it to stretch over
more
than 2, maybe, 3 pages wide, if there are only about 40 columns.

Dean

"Dave Peterson" wrote in message
...
I recorded a macro when I change the center header to "asdf" and used a
font
size of 72.

This was the important part of that recorded macro:

With ActiveSheet.PageSetup
.CenterHeader = "&72asdf"
End With

So my code changed to:

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)
HeaderStr = "&72" '<--start with that font size string.
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

72 point seems a little large to me, though.

Dean wrote:

HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20
other
variations (all to no avail), but since I did it (recorded a macro
that
enlarged the font size) on text that was already within the text box,
it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean

"Dave Peterson" wrote in message
...
Ahhhh.

There's a bug in the code.

It keeps appending the stuff in A1:A4 to the same headerstr
variable.

It should be reset to "" for each worksheet:

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)
headerstr = "" '<---- added
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

And I find it better to adjust the headers for all the sheets that
need
them.
There's nothing in this event (workbook_beforeprint) that tells you
what
sheet
(or sheets) is being printed.

And if you record a macro when you set the font, you should see the
code
necessary to change the size.

You can initialize that headerstr with that code:

headerstr = "whateveryougetfromyourrecordedmacro"

You'll have to do a little work to get it going.

Dean wrote:

Indeed, it looks like the headings may be too long after all, but
that
seems
to be happening because each time the macro is invoked, it adds yet
another
heading to the one it established the last time the macro was run
(it
may
max out at twice). Also, the headings that are already there seem
to
be
from cell a1:a4 of only the first sheet. I guess this is somewhat
consistent with what you're telling me about what the macro is
doing.

Back to the original intent. I wanted it to only do this for only
the
active sheet I am on, when I hit the print icon. Kindly edit the
macro
as
needed.

Lastly, back when I added the macro, I had some huge font sizes and
a
small,
e.g. 25% EXCEL resolution. I don't know if this is why but the
headings
printed via this macro are microscopic, something I don't want.
Can
you
edit the macro to make them bigger?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in message
...
About the print previews: I was just suggesting that as a way to
test
the
macro. So you don't have to actually waste paper.

Your intent to apply this to just the worksheet you're printing
is
not
what the
code does. The code processes all the sheets that have names in
that
array--whether you're printing or not.

And if you know the name of the sheet that's causing the trouble,
I
think
that
the next step is to look at the stuff in A1:A4 of that sheet.
And
then
to
look
at what's in the left and right header for that worksheet.



Dean wrote:

I think I just posted an empty response - sorry! Let's try
again.

We're only talking about 100 characters or less in the header,
so I
doubt
that's the problem

I am confused by your debugging suggestion. My intent was to
apply
the
macro to only whatever worksheet I was trying to print and, as
far
as
I
can
tell (because it does seem to print it, even perhaps with no
problems,
after
I hit end, after the macro crashes), it just prints the one
worksheet.
So I
know which sheet is causing the problem. By printing the other
two
sheets,
I can tell you that the problem occurs on each sheet. Or do you
think
I
am
just so confused?!!

Also, I am confused by your PS. Are you simply suggesting that
I
should
try
to minimize my print previews?

Thanks, Dave!
Dean

"Dave Peterson" wrote in message
...
Maybe it's not the code that's got the problem.

There is a limit of how much stuff can go in headers and
footers.

IIRC, the total number of characters in all 3 Headers is 255.
(Same
with
Footers.)

If that's not the problem, then maybe it's something in one of
the
cells
that's
causing the damage.

Add a line that may help you debug the problem:

With Worksheets(mySheetNames(iCtr))
msgbox .name

Then you'll know which one blows up when you're testing.

ps. The code runs when you do a print|preview, too. You may
want
to
save
some
paper.

Dean wrote:

Sorry bout that!

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

"Dave Peterson" wrote in message
...
I don't see the code you used.

Dean wrote:

Very nice! I have attached the new macro, in case what I
edited
came
out
differently than what you wrote, because it is still
bombing
out
at
the
same
point - the page setup command.

The error is 1004 and it says it is unable to set the
Centerheader
property
of the PageSetup class. Any idea what the problem could
be?

Thanks again, Dave!
Dean

"Dave Peterson" wrote in
message
...
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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson