I have a macro that insert an empty row or a table header when met a certain condition at the end of each HPagebreak.
My problem is the macro only works until the calculated page break at the initial of the code. (i.e: macro calculate total HPageBreak = 72)
The macro doesn't include if additional page created later (I.e: pg. 73 onwards) due to the addition of rows from the condition met by the code.
How do I change the code to include the added extra page?
Below is the code. This code is base on the compilation of codes I search in the internet.
Code
Option Explicit
Sub InsertRowPageBreak()
'Short cut key - CTRL + SHIFT + I
'Declare all the variants
Dim ws As Worksheet
Dim rng As Range
Dim pb As Variant
Dim Activesheets As Range
'Disable screen updating
Application.ScreenUpdating = False
'Change application to manual calculation
Application.Calculation = xlCalculationAutomatic
'Find page break
ActiveWindow.View = xlPageBreakPreview
'Start declaring the initial start of the rng cell - Column C
For Each pb In ActiveSheet.HPageBreaks
Set rng = ActiveSheet.Range("C" & pb.Location.Row)
'Condition 1: Do nothing if header is the first row of the page. (Check for row is not blank and 1 row above is blank)
With Application
If .CountA(rng.EntireRow) <> 0 And .CountA(rng.Offset(-1, 0).EntireRow) = 0 Then
'Condition 2: Insert row before table header if the table header is the last row of the page.
ElseIf .CountA(rng.EntireRow) <> 0 And .CountA(rng.Offset(-1, 0).EntireRow) <> 0 _
And .CountA(rng.Offset(-2, 0).EntireRow) = 0 Then
rng.Offset(-1, 0).EntireRow.Insert
rng.Offset(-2, 0).EntireRow.Borders(xlEdgeTop).LineStyle = xlNone
'Condition 3: Insert row to the 1st row of the page if the page break falls in between data. (Model table)
ElseIf .CountA(rng.EntireRow) <> 0 And .CountA(rng.Offset(-1, 0).EntireRow) <> 0 _
And .CountA(rng.Offset(-2, 0).EntireRow) <> 0 And rng.Offset(0, -1).Value <> "" Then
rng.EntireRow.Insert
Workbooks("MacroInsertPageBreak.xlsm").Worksheets("Source").Rows(4).Copy Destination:=ActiveSheet.Rows(rng.Offset(-1, 0).Row)
'Condition 4: Insert row to the 1st row of the page if the page break falls in between data. (Part list table)
ElseIf .CountA(rng.EntireRow) <> 0 And .CountA(rng.Offset(-1, 0).EntireRow) <> 0 _
And .CountA(rng.Offset(-2, 0).EntireRow) <> 0 And rng.Offset(0, -1).Value = "" Then
rng.EntireRow.Insert
Workbooks("MacroInsertPageBreak.xlsm").Worksheets("Source").Rows(2).Copy Destination:=ActiveSheet.Rows(rng.Offset(-1, 0).Row)
End If
End With
Next pb
MsgBox "Insert Page break complete!!"
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub