Help on VBA Page Breaks

[Apollyon]

I am hoping that someone can help me with a problem I am having with
the page breaks on Excel 2003. My macro builds a report and copies the
report to a set range on a spreadsheet. Based on the customer, the
number of similar reports generated will vary. As I build the reports
and set manual page breaks, for the first two reports everything works
fine. Starting with the third iteration, an automatic pagebreak is
inserted into the print area and then all subsequent reports are
offset. When it comes time to print the report, the print area is
affected and then forces me to manually set page breaks in order to
keep track of the number of pages.

How can I build multiple reports and force the page breaks to fall
where I want them?

 

[Tom Ogilvy]

Not sure how you would insert an automatic pagebreak, but you need to insert
manual pagebreaks. Automatic pagebreaks appear automatically in locations
where the printer will break the print job. They can not be overridden -
but can be controlled by inserting a manual pagebreak somewhere before the
automatic pagebreak (which will then be moved if necessary).

 

[Apollyon]

Tom:

Thanks for your reply. I am not sure why your suggestion does not work,
but for this application it does not appear to resolve the issue. As I
tried to outline before, when I write the first report, I force a
manual page break on each of the first two pages. The next iteration
of the macro writes the second report (starting at the end of the first
report) and then forces an additional two manual page breaks. On the
third iteration, an auto page break is inserted, and the third report
then starts after several rows from the end of the second report. It is
acting as if when I manually insert the final page break for the second
report that the "next address" for the beginning of the third report is
shifted to reflect inserting the manual page break.

Have you ever encountered something like I am describing?

 

[Tom Ogilvy]

the third report should start directly after the final manual pagebreak of
the second report. If the final manual pagebreak for the 2nd report is
inserted at row 200, then row 200 would be the first row on the third
report.

If you set your print area and it doesn't correspond to row 200 then this
could be problematic.

 

[Apollyon]

The macro sets the next address using the "Range.Offset.Select" and
then uses this saved address to start the next report. It appears that
when I insert a manual page break and then select the activecell to set
the starting range that the effect of inserting the page break has
changed the stored address for the beginning of the next report. Here
is a snipet of the code I am using:

' Build first manifold report
If i = 1 Then
Range("A274").Select
ManifoldHeaderRange.Copy ActiveCell
Manifold_General_InspectRange.Copy ActiveCell.Offset(18, 0)
Range("A330").Select
ActiveCell.PageBreak = xlPageBreakManual
Range("A364").Select
Set NextAddress = ActiveCell
ActiveCell.PageBreak = xlPageBreakManual
ManifoldReport_Pointer = ManifoldReport_Pointer + 1
Else
NextAddress.Select
ManifoldHeaderRange.Copy ActiveCell
Manifold_General_InspectRange.Copy ActiveCell.Offset(18, 0)
ActiveCell.Offset(56, 0).Select
ActiveCell.PageBreak = xlPageBreakManual
ActiveCell.Offset(90, 0).Select
Set NextAddress = ActiveCell
ActiveCell.PageBreak = xlPageBreakManual
ManifoldReport_Pointer = ManifoldReport_Pointer + 1
End If

 

[Tom Ogilvy]

If the 2nd and 3rd sheet should be set up by like the first, then

ActiveCell.Offset(90, 0).Select

should be

ActiveCell.Offset(34, 0).Select

 

[Apollyon]

Tom:

THANK YOU!