Setting print areas

[AMK4]

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]

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).

--
**** 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.

[AMK4]

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.

[NickHK]

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" <[email protected]> wrote in message
news:[email protected]...
>
> **** 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]

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.

[NickHK]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>