>Hi,

>I hope somebody can help me with this problem.  I have a spreadsheet
>that haas varying lengths of reports depending on
>information entered in the spreadsheet.  I have been able to generate
>the different reports depending on the information.  The
>problem that I need help solving is inserting Horizontal Page Breaks
>into the report if the length of the report is greater than a
>set amount.  I know what the set length of the report that I am using as
>the limit and how many pages that the report needs to
>be.  I know that I have to use a Select Case statement to choose the
>proper page break insertion points depending on how
>many pages the report is.  Here is the scenario:

>1}    I have the Print Area set to a specific range that does NOT change
>no matter which report is being printed.
>2}    I have determined how long the report is through a For Next loop
>adding up the height of each row in points.
>3}    I know the limit that I want to have on one page.  It is 1300
>points.  If the report is longer than 1300 points, I set the
>number of pages required to 2 instead of 1.
>4}    I know where the page break has to be inserted if the report has
>to be on 2 pages.

>What I need to know is how to set the page breaks.  Here is the code
>that I have tried:

>Sub Print_Routine(PrintCopy As Integer, PageSplit As String)
>'
>' Print_Routine Macro
>' Printing routine that determines the number of pages the
>' report should be spread over and sets up the page properly.
>'
>Worksheets("Sheet1").ResetAllPageBreaks
>ReportPages = 1
>Select Case PageSplit
>    Case "Y"
>        ReportPoints = 0
>        ReportRows = Worksheets("Sheet1").Range("Print_Area").Rows.Count

>        For I = 1 To ReportRows
>            b = Worksheets("Sheet1").Range("Print_Area").Rows(I).Height
>            ReportPoints = ReportPoints + b
>        Next I
>        If ReportPoints > 1299 Then
>            ReportPages = 2
>        End If
>    Case "N"
>        ReportPages = 1
>End Select
>With Worksheets("Sheet1").PageSetup
>    .Zoom = False
>    .FitToPagesWide = 1
>    .FitToPagesTall = ReportPages
>    .LeftMargin = Application.InchesToPoints(0.5)
>    .RightMargin = Application.InchesToPoints(0.5)
>    .TopMargin = Application.InchesToPoints(0.5)
>    .BottomMargin = Application.InchesToPoints(0.5)
>    .Orientation = xlPortrait
>    .PaperSize = xlPaperLegal
>End With
>Select Case ReportPages
>    Case 2
>        Worksheets("Sheet1").HPageBreaks.Add
>Before:=Worksheets("Sheet1").Range("A72")
>End Select
>Worksheets("Sheet1").PrintOut Copies:=PrintCopy
>Select Case ReportPages
>    Case 2
>        ReportPages = 1
>        Worksheets("Sheet1").ResetAllPageBreaks
>End Select
>End Sub

>What happens when there is two pages to print is the application
>calculates where the page breaks should be because of the
>FitToPagesTall parameter.  I have inserted break points and checked the
>cell A72 to find that there is a page break inserted
>there but it does not show up when the Page Break Preview is selected.
>I have tried a couple different ways but have not yet
>been able to solve the problem.

>If you think you might be able to help, please respond to my email
>address and to the Newgroup.

>Thanks in advance,

>Trevor Schellhorn