pagebreaks

[philfer]

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]

Worked fine in my simple test Phil. Can you post the workbook?

[philfer]

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]

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]

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.