Short story: I have a sheet that's dynamically created/populated with
data coming from a "template sheet". What's the best way to figure out
the proper page setup for printing?

Long story: Sheet 1 has a column of information that will be used to
populate a template. Sheet 2 has the template (A1:Q18) that gets
copied and dumped on Sheet 3 and then populated with row information
from Sheet 1. Rinse, lather, repeat for however many rows of data
exists on Sheet 1.

When I print manually, I can take three of those blocks (A1:Q18,
A19:Q18, A37:Q18) and print them on one paper sheet. How can I figure
out what will fit on a sheet and set the print size accordingly through
VBA? so that someone else using this can just hit print and have the
page setup already be set for them.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=509182

AMK4:

I don't know if I completely understand your question. If you want the
print area to adjust to the size of your data, you can define a worksheet
level name. Insert Name Define - put Sheet3!Print_Area in the name box.
In the refersto box, put

=Offset(A1,0,0,counta($a:$a),17)

Now the print area will be however many entries you have in column A and 17
column wide (Q).

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

AMK4 wrote:
Short story: I have a sheet that's dynamically created/populated with
data coming from a "template sheet". What's the best way to figure
out the proper page setup for printing?

Long story: Sheet 1 has a column of information that will be used to
populate a template. Sheet 2 has the template (A1:Q18) that gets
copied and dumped on Sheet 3 and then populated with row information
from Sheet 1. Rinse, lather, repeat for however many rows of data
exists on Sheet 1.

When I print manually, I can take three of those blocks (A1:Q18,
A19:Q18, A37:Q18) and print them on one paper sheet. How can I figure
out what will fit on a sheet and set the print size accordingly
through VBA? so that someone else using this can just hit print and
have the page setup already be set for them.

Dick Kusleika Wrote:
AMK4:

I don't know if I completely understand your question. If you want
the
print area to adjust to the size of your data, you can define a
worksheet
level name. Insert Name Define - put Sheet3!Print_Area in the name
box.
In the refersto box, put

=Offset(A1,0,0,counta($a:$a),17)

Now the print area will be however many entries you have in column A
and 17
column wide (Q).

Not quite. Ok, let's see if I can explain it better:

Sheet 1 has a column with names in it. For the sake of this
experiment, let's say the names are all in column D.

Sheet 2 has a form designed on it that covers cells A1:Q18.

Now, I have a VBA macro that will:
- loop through all the names on Sheet 1, column D
- for each name, it copies the form from Sheet 2 and dumps it on
Sheet 3, and inserting the name on the form.
- Moves on to the next name on the list.

When it's done, if I simply print out Sheet 3, I will end up with page
breaks in the middle of some of the cells that contain stuff, like some
of the forms will be split onto two pages. I want to be able to have it
(programmatically) set how many forms per sheet of paper (page break) it
will print, instead of having the user have to go into View - Page
Break Preview and manually have to adjust the page breaks so that none
of the forms get cut off.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=509182

AMK4,
Does this get you closer:
Dim i As Long
Const ROWS_PER_PAGE As Long = 17 'Or whatever suits you

With ActiveSheet
.ResetAllPageBreaks
For i = ROWS_PER_PAGE To .UsedRange.Rows.Count Step ROWS_PER_PAGE
.HPageBreaks.Add .Cells(i, "A")
Next
End With

NickHK

"AMK4" wrote in message
...

Dick Kusleika Wrote:
AMK4:

I don't know if I completely understand your question. If you want
the
print area to adjust to the size of your data, you can define a
worksheet
level name. Insert Name Define - put Sheet3!Print_Area in the name
box.
In the refersto box, put

=Offset(A1,0,0,counta($a:$a),17)

Now the print area will be however many entries you have in column A
and 17
column wide (Q).

Not quite. Ok, let's see if I can explain it better:

Sheet 1 has a column with names in it. For the sake of this
experiment, let's say the names are all in column D.

Sheet 2 has a form designed on it that covers cells A1:Q18.

Now, I have a VBA macro that will:
- loop through all the names on Sheet 1, column D
- for each name, it copies the form from Sheet 2 and dumps it on
Sheet 3, and inserting the name on the form.
- Moves on to the next name on the list.

When it's done, if I simply print out Sheet 3, I will end up with page
breaks in the middle of some of the cells that contain stuff, like some
of the forms will be split onto two pages. I want to be able to have it
(programmatically) set how many forms per sheet of paper (page break) it
will print, instead of having the user have to go into View - Page
Break Preview and manually have to adjust the page breaks so that none
of the forms get cut off.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=509182

NickHK Wrote:
AMK4,
Does this get you closer:
Dim i As Long
Const ROWS_PER_PAGE As Long = 17 'Or whatever suits you

With ActiveSheet
.ResetAllPageBreaks
For i = ROWS_PER_PAGE To .UsedRange.Rows.Count Step ROWS_PER_PAGE
.HPageBreaks.Add .Cells(i, "A")
Next
End With

NickHK

Well, not quite. This just adds a pagebreak after each part, when it
could easily fit up to 3 parts on one printed sheet.

My other problem right now is trying to figure out how to set a
vertical pagebreak. I figured out to set one manually, but I can't
figure out how to actually tell it to get rid of the one
(automatically) placed on a different column.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=509182

AMK4,
Well increase the ROWS_PER_PAGE to 3 *17.
As for VPageBreaks, you cannot remove automatic page breaks, obviously. It's
there because that is the edge of the printable area.

NickHK

"AMK4" wrote in message
...

NickHK Wrote:
AMK4,
Does this get you closer:
Dim i As Long
Const ROWS_PER_PAGE As Long = 17 'Or whatever suits you

With ActiveSheet
.ResetAllPageBreaks
For i = ROWS_PER_PAGE To .UsedRange.Rows.Count Step ROWS_PER_PAGE
.HPageBreaks.Add .Cells(i, "A")
Next
End With

NickHK

Well, not quite. This just adds a pagebreak after each part, when it
could easily fit up to 3 parts on one printed sheet.

My other problem right now is trying to figure out how to set a
vertical pagebreak. I figured out to set one manually, but I can't
figure out how to actually tell it to get rid of the one
(automatically) placed on a different column.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=509182