Insert horizontal pagebreak like view tab

[YasserKhalil]

Hello everyone
I am trying to set page breaks at specific points through the code and I used this

Code: Select all

sh.Range("A" & sh.Cells(Rows.Count, 2).End(xlUp).Row + 1).PageBreak = xlPageBreakManual

I also at the start of the code reset the page breaks so as to start in clean way

Code: Select all

        With sh.Range("A9:G" & Rows.Count)
            .UnMerge: .ClearContents: .Borders.Value = 0
            .Parent.ResetAllPageBreaks
        End With

I expect to have two pages but I got four pages .. The weird thing : the page breaks are set correctly but when I click preview for print I have found 4 pages not two pages

Any ideas?

[HansV]

Sample workbook?

[YasserKhalil]

Here's the sample and in the attachment the code is already there

[HansV]

Apart from the two manual page breaks, there are two automatic page breaks because the data don't fit.
You can make everything fit on two pages by adding the following above Application.ScreenUpdating = True:

Code: Select all

        With sh.PageSetup
            .Zoom = False
            .FitToPagesTall = 2
        End With

[YasserKhalil]

Thank you very much Mr. Hans

Is there a way to prevent the automatic page breaks ..?
How can I simulate what I am doing on the View tab >> Page Break Preview that enables me to control all the page breaks manually ...

[HansV]

Excel will always add automatic page breaks if necessary based on the page setup that you specify.