• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Debug Help


New Member
Hello, I am using the below formula to create a new page after every page break. When I run this it works well until the last page break. I get the Debug error and then it does not put the information after the last page break into the worksheet.

Public Sub Copy_Each_Page_Break_Section_To_New_Worksheet()

    Dim reportWorksheet As Worksheet
    Dim saveActiveCell As Range
    Dim lastRow As Long, pageStartRow As Long
    Dim page As Long
    Dim newWorksheet As Worksheet
    'Look on the active sheet in the active workbook
    Set reportWorksheet = ActiveWorkbook.ActiveSheet
    Set saveActiveCell = ActiveCell
    With reportWorksheet
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        pageStartRow = 1
        'Copy rows in each page break section to new worksheet
        For page = 1 To .HPageBreaks.Count
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            newWorksheet.Name = "Page " & page
            .Rows(pageStartRow & ":" & .HPageBreaks(page).Location.Row - 1).EntireRow.Copy newWorksheet.Range("A1")
            pageStartRow = .HPageBreaks(page).Location.Row
        If pageStartRow <= lastRow Then
            'Copy rows after last page break to new worksheet
            Set newWorksheet = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
            newWorksheet.Name = "Page " & page
            .Rows(pageStartRow & ":" & lastRow).EntireRow.Copy newWorksheet.Range("A1")
        End If
    End With
    'Restore active cell
End Sub



Excel Ninja
Staff member
It runs flawlessly for me, no errors?

Have you put the code in the Sheet1 worksheet object, like shown below or in a code module?

It needs to be in the Worksheet object