Problems Using Page Breaks in Excel 97 from Visual Basic
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
When there is two pages to print, 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.
When the report is sent to the printer, what I see on the screen is what
ends up on the printer. I could manually record the macro for the part
that moves the automatic page break but when the number of lines on the
report changes, I would have to re-record the macro to move the
automatic page break. 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
