locked
Trying to create page breaks RRS feed

  • Question

  • I'm trying to create page breaks after every 20 rows, minus the top row (headers) but I'm not sure how to fix the code. This is just something I found on conditional pg breaks, and haven't set it for groups yet.

        Dim CellRange As Range
        Dim TestCell As Range

        ActiveSheet.ResetAllPageBreaks

        Set CellRange = Selection
        For Each TestCell In CellRange
            If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
                ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual
            End If
        Next TestCell
    End Sub


    • Edited by Aquilam Tuesday, November 29, 2016 9:07 PM
    Tuesday, November 29, 2016 9:04 PM

All replies

  • Hi,

    You can use this function 

    Here For loop is starting from 21 because you mentioned the first page break should be 20+1 (header). This you can adjust it. The number 20 which is mentioned after "Step" that is the interval. You can adjust that as well as per youir need.


    Sub PageBreakAfter20()
        Dim totalRange As Range
        ActiveSheet.ResetAllPageBreaks
        Set totalRange = ActiveSheet.UsedRange
        For irow = 21 To totalRange.Rows.Count Step 20
           Rows(irow).PageBreak = xlPageBreakManual
        Next
    End Sub
    


    Vish Mishra

    Tuesday, November 29, 2016 9:35 PM
  • Hi Aquilam,

    you can use the code mentioned below.

    which will break the page after 20 rows and also take care of Heading.

    Sub Insert_PageBreaks()
        Dim Lastrow As Long
        Dim Row_Index As Long
        Dim RW As Long
        RW = 20
        With ActiveSheet
            .ResetAllPageBreaks
            Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
            For Row_Index = RW + 2 To Lastrow Step RW
                .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
            Next
        End With
    End Sub
    

    Below is the output you will get for each page 20 rows. I have merge all pages in 1 image.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 30, 2016 4:46 AM