VBA - Adding Page Breaks at Row Intervals

[SimonGeoghegan]

Hi All,

I have the following code which I believe should add page breaks at every 20th line of a worksheet. However, this does not seem to be applying when I check both the "Print Preview" screen or the "Page Break Preview".

    Dim Row_Index As Long
    Dim RW As Long

    RW = 20
    
    With ActiveSheet
        .ResetAllPageBreaks
            For Row_Index = RW + 6 To LastRow2 Step RW
                .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
        Next
    End With

I effectively had an action plan which could, over time, become very large, and therefore I want the user to be able to print this off to show 20 lines per page, alongside rows 1-6 which will be repeated.

The screenshot hopefully shows both the code as I'm stepping through, and also the lack of page breaks. I also show the "Row_Index" in the immediate window to show its currently set to line 26.

I assume I am missing something, but could someone possibly point me in the right direction?

Regards,
Simon

 

[gallen]

I believe you just need the row for the add method. Untested:

    Dim Row_Index As Long
    Dim RW As Long

    RW = 20
    
    With ActiveSheet
        .ResetAllPageBreaks
            For Row_Index = RW + 6 To LastRow2 Step RW
                .HPageBreaks.Add Before:=.Rows(Row_Index)
        Next
    End With

 

[SimonGeoghegan]

Thanks Gallen, I've just amended that but unfortunately still no joy - the outcome is the same as my original post I'm afraid.

 

[gallen]

Strange. I just tried it on a sheet and definitely creates the page breaks.

Looking at this line

For Row_Index = RW + 6 To LastRow2 Step RW

Please check what value is stored in the variable 'LastRow2' I assumed it was the last used row in column A but you don't set it or declare it in the code you provided

 

[SimonGeoghegan]

Hi Gallen, this is the code for LastRow2:

Dim LastRow2 As Long
    LastRow2 = Range("B" & Rows.Count).End(xlUp).Row

Within the Immediate Window, querying

? LastRow2

provides a value of 46. Further use of the code (which creates additional lines) will mean that this then changes to 66, 86, 106 and so on.

Thanks in advance

 

[SimonGeoghegan]

Also, if it helps, my entire code is:

Sub AddLinesActionPlan()

Dim LastRow As Long
Dim LastRow2 As Long

Application.ErrorCheckingOptions.BackgroundChecking = False

Sheets("Clinical Action Plan").Unprotect

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Range("B7:M26").Copy
Range("B6").End(xlDown).Offset(1, 0).PasteSpecial

LastRow2 = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LastRow + 1, "J" & LastRow2).Select
Selection.ClearContents

Range("L" & LastRow + 1, "M" & LastRow2).Select
Selection.ClearContents

Range("B7").AutoFill Destination:=Range("B7:B" & LastRow2), Type:=xlFillSeries
Range("K7").AutoFill Destination:=Range("K7:K" & LastRow2), Type:=xlFillSeries
Range("A1").Select

Dim Row_Index As Long
Dim RW As Long
    RW = 20
    
    With ActiveSheet
        .ResetAllPageBreaks
            For Row_Index = RW + 6 To LastRow2 Step RW
                .HPageBreaks.Add Before:=.Rows(Row_Index)
        Next


Sheets("Clinical Action Plan").Protect

MsgBox ("20 additional lines now added.")

End Sub

 

[gallen]

I'm at a loss. Without understanding exactly what you expect to see and what actually happens, the code executes fine for me, each line doing as it should . Adding a page break initially at rows 25-26 then rows 45-46 and so on

 

[gallen]

The above results were done using this code:

Sub AddLinesActionPlan()

Dim LastRow As Long
Dim LastRow2 As Long

Application.ErrorCheckingOptions.BackgroundChecking = False

Sheets("Clinical Action Plan").Unprotect

LastRow = Range("B" & Rows.Count).End(xlUp).Row

Range("B7:M26").Copy
Range("B6").End(xlDown).Offset(1, 0).PasteSpecial

LastRow2 = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LastRow + 1, "J" & LastRow2).Select
Selection.ClearContents

Range("L" & LastRow + 1, "M" & LastRow2).Select
Selection.ClearContents

Range("B7").AutoFill Destination:=Range("B7:B" & LastRow2), Type:=xlFillSeries
Range("K7").AutoFill Destination:=Range("K7:K" & LastRow2), Type:=xlFillSeries
Range("A1").Select

Dim Row_Index As Long
Dim RW As Long
    RW = 20
    
    With ActiveSheet
        .ResetAllPageBreaks
        For Row_Index = RW + 6 To LastRow2 Step RW
            '1st iteration places pagebreak between rows 25 and 26
            '2nd iteration places page break between 45 and 46
            'and so on every 20 used rows.
            .HPageBreaks.Add Before:=.Rows(Row_Index)
        Next

    End With
Sheets("Clinical Action Plan").Protect

MsgBox ("20 additional lines now added.")

End Sub

 

[SimonGeoghegan]

Thanks Gallen. I've used your code to test myself but still not working unfortunately.

The only page break I have visible to me is at the very end of the range, rather than every 20 lines.

I had some named ranges in relation to print area, which I've now deleted (these were offsets, so didn't know if they would impact upon the code). Still no joy however.

I'm at a bit of a loss now as I can see clearly that it works for you.

 

[gallen]

Last thing to do (and is always good practice) is avoid using "Active" anything where possible

Change this line:

With ActiveSheet

to

With Sheets("Clinical Action Plan")