Do While Nested Loops

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