Dynamic Page breaks - VBA

[eoinymc]

Hi,

I have a sheet that I want to dynamically set the page break within a dynamically defined print area.

Here is the code I have:

Table1.PageSetup.PrintArea = "$A$1:$R$" & FRBottom + 3    With Table1.PageSetup
        .PrintTitleRows = "$1:$7"
        .LeftMargin = Application.InchesToPoints(0.15748031496063)
        .RightMargin = Application.InchesToPoints(0.15748031496063)
        .TopMargin = Application.InchesToPoints(0.47244094488189)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0.511811023622047)
        .Orientation = xlPortrait
        .CenterHorizontally = True
    End With
    
    Set Table1.VPageBreaks(1).Location = Table1.Range("R1")
    Set Table1.HPageBreaks(1).Location = Table1.Cells(FRBreak + 1, 1)

Where FRBottom is a variable which is the bottom row of the print area and FRBreak is the variable where I want to set the page break.

Do I need the VPageBreaks line of code?

What I'm looking for is the first page to be A1:R-FRBreak and the second page to be A-FRBreak+1:R-FRBottom

Anyone able to help me?

Thanks,

Eoin

 

[Domenic]

I'm assuming that in some instances the print area may not contain a horizontal page break. Therefore, you'll need to add one at the desired location, instead of moving the first horizontal page break to the desired location. Try replacing...

Set Table1.HPageBreaks(1).Location = Table1.Cells(FRBreak + 1, 1)

with

Table1.HPageBreaks.Add Table1.Cells(FRBreak + 1, 1)