View Full Version : pagebreaks
philfer
12-15-2010, 11:07 AM
Hello,
I am trying to format the printing of a sheet so that there is a page break every 39 rows.
I do :-
With Worksheets("ExcessReport")
.Activate
LastRow = .Range("F" & .Rows.Count).End(xlUp).Row
.PageSetup.PrintArea = "$A$1:$I$" & LastRow
.ResetAllPageBreaks
For i = 39 To LastRow Step 39
.Rows(i).PageBreak = xlPageBreakManual
Next i
End With
But when I look in page break preview there are page breaks at row 45 and 90 and none on row 39 etc
Can anyone help
Thanks
Phil
Bob Phillips
12-15-2010, 11:37 AM
Worked fine in my simple test Phil. Can you post the workbook?
philfer
12-22-2010, 11:25 AM
Hello,
I am not allowed to post the actual workbook as it has a lot of client info in it so I made a replica of the relevant sheets.
The reason there are two sheets is that I use 1 (i.e. Sheet3) to import the data from a .dat file. I then do a text to columns operation on this data. I then select it and copy it into the report sheet (i.e. Sheet2).
The reason I try to clear the page breaks is that I re-use the workbook each day. Every day the use opens the previous days workbook presses a button 'new' which then saves the workbook down with the new date and clears all the import and report sheets reasy for a new days calculation and reporting.
The reason I use a page break every 39 rows is that the imported data is such that this cuts it off at a relevant place (the first one is set at row 40 to take account of the headers)
I hope this makes sense
Thanks for your help
Phil
p45cal
12-22-2010, 01:28 PM
Here, when printing is set to landscape I can only fit 33 rows to the page, so there are extra pagebreaks. To get 39 lines to fit I had to reduce the .PageSetup.Zoom to 85. Otherwise it ran perfectly.
Bob Phillips
12-22-2010, 02:44 PM
As p45cal hinted, because you have the zoom factor set to 100, it can't get 39 rows on a page, so it inserts an implicit pagebreak at the end of the page, after line 33. But you code adds explicit pagebreaks at row 39, so you get another after a few lines. As p45cal said, changing the zoom factor to 85 fixes it.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.