CODEWITHSUNDEEP
vbaexcel
Jonathan
Jonathan

Reputation: 45

Inserting Page Break With VBA

I'm trying to insert page breaks that will allow every 8 rows of the excel sheet to be printed. The problem that I am having is that when using the "ActiveWindow.View = xlPageBreakPreview" method, the first page break occurs after row 10, and then from there on out, occurs every 8 rows. Here is the code that I am currently using:

ActiveSheet.ResetAllPageBreaks
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(2).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

With ActiveSheet.PageSetup
    .CenterHeader = "&""Chiller""&75WOC"
    .CenterFooter = "&""Chiller""&75 PLACARD"
    .PrintQuality = 600
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlLandscape
    .PaperSize = xlPaperLetter
    .Zoom = 45
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
End With

I tried adding this loop to manually add the page break, but it bogged the code way down, so I was wondering if there is a simpler solution that I am not aware of.

Any help is much appreciated.

Upvotes: 4

Views: 25616

Answers (2)

UserSuper
UserSuper

Reputation: 11

This is completely brilliant - thank you!

For those who might be interested in creating VERTICAL page breaks - eg for each year in a cash flow - I share my code as below:

Sub pagebreaks()
'insert the required page breaks automatically
' see https://stackoverflow.com/questions/51632995/inserting-page-break-with-vba

    Dim ws As Worksheet
    Dim overallendcol As Long
    Dim mycol As Long
    
'set the working sheet
    Set ws = Sheets("cf mthly")

'clear existing page breaks
    ActiveWindow.View = xlNormalView
    ActiveSheet.Cells.pagebreak = xlPageBreakNone

'establish the first new year column
    Cells(1, 1).Select
    Cells.Find(What:="Jan", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    newyearcol = ActiveCell.Column

'establish the overall last column
    Cells(1, 1).Select
    Cells.Find(What:="totals", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    overallendcol = ActiveCell.Column

'establish each new year column
    Cells(1, 1).Select
    For mycol = newyearcol To overallendcol - 1 Step 12
        ws.Columns(newyearcol).pagebreak = xlPageBreakManual
    Next mycol

End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can add page breaks like this:

Worksheets("Sheet1").HPageBreaks.Add Before:=Worksheets("Sheet1").Rows(25)
Worksheets("Sheet1").VPageBreaks.Add Before:=Worksheets("Sheet1").Columns("J")

Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual
Worksheets("Sheet1").Columns("J").PageBreak = xlPageBreakManual

And to get every 8ᵗʰ row after row 10 use a loop like

Dim iRow As Long
For iRow = 10 To LastRow Step 8
    'your page break code here
Next iRow

where LastRow is the last used row eg like

Dim LastRow As Long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row 'find last used row in column A

So something like this would be a possible result:

Option Explicit

Public Sub AddPageBreaks()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") 

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'find last used row in column A

    Dim iRow As Long
    For iRow = 10 To LastRow Step 8
        ws.HPageBreaks.Add Before:=ws.Rows(iRow)
        ws.Rows(iRow).PageBreak = xlPageBreakManual
    Next iRow
End Sub

Upvotes: 3

Related Questions