CODEWITHSUNDEEP
vbaexcel
KathH
KathH

Reputation: 39

Excel VBA insert page break every nth row

I have the below code which does everything except insert the page break after row 35. can anyone help please?

Sub PrintSet()

    Dim rs As Worksheet
    Dim sPrintArea As String

    sPrintArea = "A1:AE65"
    For Each rs In Sheets
        rs.PageSetup.Orientation = xlLandscape
        rs.PageSetup.Zoom = False
        rs.PageSetup.FitToPagesWide = 1
        rs.PageSetup.FitToPagesTall = 2
        rs.PageSetup.PrintArea = sPrintArea
        rs.HPageBreaks.Add before:=Range("A36")                               
     Next rs

End Sub

Upvotes: 2

Views: 1238

Answers (3)

Vityata
Vityata

Reputation: 43575

Rewrite the last line like this:

rs.HPageBreaks.Add before:=rs.Range("A36")

Thus, you are adding a reference rs also. It should work better. See what Microsoft says about referencing ranges: https://msdn.microsoft.com/en-us/library/office/aa221547(v=office.11).aspx

Upvotes: 2

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

This works for me. I removed the Zoom

Sub PrintSet()
Dim rs As Worksheet
Dim sPrintArea As String

    sPrintArea = "A1:AE65"
    For Each rs In Sheets
        rs.PageSetup.Orientation = xlLandscape
        rs.PageSetup.FitToPagesWide = 1
        rs.PageSetup.FitToPagesTall = 2
        rs.PageSetup.PrintArea = sPrintArea
        rs.HPageBreaks.Add before:=Range("A36")
    Next rs
End Sub

Upvotes: 0

starko
starko

Reputation: 1149

Try something like this:

Dim hpgbr As HPageBreak
Dim hpgbrs As HPageBreaks
Set hpgbr = hpgbrs.Add(Before:=Range("A36"))

Upvotes: 0

Related Questions