Do While Nested Loops

Are these going to be numbers and text?
Right now you have the entire column formatted as text

This is a two fold code, first code will insert the blanks, then call the second code to insert the "Section" part.

Sub MakeBlankRows()
    Dim LstRow As Long, x As Long
    Dim sh As Worksheet, c As Range

    Set sh = Sheets("Sample2")
    With sh
        LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For x = LstRow To 1 Step -1
            Set c = .Cells(x, 1)
            If c <> "StartHeader" Then

                If c.Offset(-1) = "N" Or IsNumeric(c) And IsNumeric(c.Offset(-1)) Then
                    If c <> c.Offset(-1) Then
                        .Cells(x, 1).EntireRow.Insert
                    End If
                End If
            End If
        Next
    End With

    Insert_Section

End Sub

Sub Insert_Section()
    Dim LstRw As Long, Rng As Range, c As Range
    Dim sh As Worksheet

    Set sh = Sheets("Sample2")

    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range("A1:A" & LstRw)
        For Each c In Rng.SpecialCells(xlCellTypeBlanks)
            c = "Section " & c.Offset(1).Value
            c.Interior.Color = vbYellow
        Next c
    End With
End Sub

See attached sample

Sub CreatePages()

    Dim data As Range
    Dim rowIndex As Long
    Dim pageCount As Long
    Dim startHeaderRow As Long
    Dim nRowIndex As Long
    Dim inPages As Boolean
    Dim pages As Range
    
    Set data = Range("A1", ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp))
    startHeaderRow = 1
    pageCount = 0
    rowIndex = 2
    inPages = False
    Set pages = Nothing
    Do While rowIndex <= data.Rows.Count
        If data.Cells(rowIndex, 1) = "N" Then
            rowIndex = rowIndex + 1
            data.Cells(rowIndex, 1).Insert shift:=xlShiftDown
            pageCount = 1
            Set pages = Cells(rowIndex, 1)
            inPages = True
            rowIndex = rowIndex + 1
        ElseIf data.Cells(rowIndex, 1) = "StartHeader" Then
            If Not pages Is Nothing Then
                With pages
                    .NumberFormat = "General"
                    .FormulaR1C1 = "=""Page "" & (COUNTIF(R" & startHeaderRow & "C1:R[-1]C,""Page*"")+1) & "" of " & pageCount & """"
                End With
            End If
            startHeaderRow = rowIndex
            pageCount = 0
            inPages = False
            Set pages = Nothing
        ElseIf inPages Then
            If Cells(rowIndex, 1) <> Cells(rowIndex - 1, 1) Then
                data.Cells(rowIndex, 1).Insert shift:=xlShiftDown
                pageCount = pageCount + 1
                Set pages = Union(pages, Cells(rowIndex, 1))
                rowIndex = rowIndex + 1
            End If
        End If
        rowIndex = rowIndex + 1
    Loop
    
    If Not pages Is Nothing Then
        With pages
            .NumberFormat = "General"
            .FormulaR1C1 = "=""Page "" & (COUNTIF(R" & startHeaderRow & "C1:R[-1]C,""Page*"")+1) & "" of " & pageCount & """"
        End With
    End If
    
End Sub

This is a messy code, but it works if I'm not mistaken to get what you mean :

Sub test()
'Range("D:D").ClearContents
lr = Columns(1).Rows.Count
Set Rng1 = Range("A:A")
Set oTarget = Sheets("Sample2").Range("D" & lr) 'change as your need (in your workbook is Sheet Output column A)

Set c = Rng1.Find("StartHeader", LookAt:=xlPart, After:=Range("A" & lr))
If Not c Is Nothing Then
FirstAddress = c.Address
        Do
        Set oFill = oTarget.End(xlUp).Offset(1, 0)
        Set oPage = oFill
        Range(c, c.Offset(5, 0)).Copy Destination:=oFill
        Set check1 = c.Offset(6, 0)
        Set x = check1
            Do
            FirstCheck = x.Value
            Set check2 = x
            Set x = x.Offset(1, 0)
                    If x.Value <> FirstCheck Then
                    Set oFill = oTarget.End(xlUp).Offset(1, 0)
                    oFill.Value = "Page " & FirstCheck
                    Range(check1, check2).Copy Destination:=oFill.Offset(1, 0)
                    Set check1 = x
                    End If
                    
                    If x.Value = "" Then
                    Set rngPage = Range(oPage, oFill)
                        For Each cell In rngPage
                        If InStr(cell, "Page") Then _
                        cell.Value = cell.Value & " of " & FirstCheck
                        Next
                        GoTo finish
                        End If
            Loop Until InStr(x, "StartHeader")
             
            Set rngPage = Range(oPage, oFill)
            For Each cell In rngPage
            If InStr(cell, "Page") Then _
            cell.Value = cell.Value & " of " & FirstCheck
            Next

        Set c = Rng1.FindNext(c)
        Loop While c.Address <> FirstAddress
End If

finish:
Sheets("Sample2").Range("D1").Delete Shift:=xlUp 'change as needed
'Range("A:C").EntireColumn.Delete --->unmark this if you want the output is in the same sheet.
End Sub

Oopss... sorry, I didn't realize that what you want is to insert entire row .

Here is the code which do the insert.

Sub test2()
lr = Columns(1).Rows.Count
Set Rng1 = Range("A:A")

Set c = Rng1.Find("StartHeader", LookAt:=xlPart, After:=Range("A" & lr))
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Set check1 = c.Offset(6, 0)
c.Offset(6, 0).EntireRow.Insert
n = n + 1
c.End(xlDown).Offset(1, 0).Value = "Page" & n
Set check = check1.Offset(1, 0)

Do
If check.Value <> check1.Value Then
If check.Value = "StartHeader" Then Exit Do
If check.Value = "" Then
Set Rng = Range(c, check)
For Each cell In Rng
If InStr(cell, "Page") Then cell.Value = cell.Value & " of " & n
Next
Exit Sub
End If
n = n + 1
check.EntireRow.Insert
Set RngPage = c.End(xlDown).Offset(1, 0)
c.End(xlDown).Offset(1, 0).Value = "Page" & n
End If
Set check1 = check
Set check = check1.Offset(1, 0)
Loop

Set Rng = Range(c, check)
For Each cell In Rng
If InStr(cell, "Page") Then cell.Value = cell.Value & " of " & n
Next
n = 0
Set c = Rng1.FindNext(c)
Loop While c.Address <> FirstAddress
End If
End Sub

This code is assuming that each "number" has the same value. For example :
the number "1" in your example (under page 1 of n) value is 1_aaa, the next row is also 1_aaa
So if the number "1" value is : 1_aaa, 1_aab, and so on, the code will fail
because the code doesn't check the number but the value.

StartHeader		
1		
R		
R		
R		
N	FAIL	 WORK
1	1aaa	 1aaa
1	1aab	 1aaa
1	1aac	 1aaa
1	1aad	 1aaa
2	2xx1	 2xx1
2	2xx2	 2xx1
2	2xx3	 2xx1
2	2xx4	 2xx1