Set Page Break Cell Value Equals X with VBA

[jwb1012]

Hello everyone, I am attempting to use the code below to insert a page break above every row where column C = "BOE Summary",

But, the current code results in a Run Time Error 13: Type Mismatch. I would also like to set the print area from cell C2: "J" & End_Row (column J and the last row with data). And start the page break insertion beginning with the 2nd occurrence of "BOE Summary".

Any assistance would be much appreciated.

Sub PageBreaks()
Dim End_Row As Long, n As Long, Ins As Long
Dim Sh As Worksheet
    Application.ScreenUpdating = False
    
    End_Row = ActiveSheet.Range("L" & Rows.Count).End(xlUp).Row
        
        For n = End_Row To 3 Step -1
            Ins = ActiveSheet.Cells(n, "C").Value
            
            If Ins = "BOE Summary" Then
                Sh.Range(n).PageBreak = xlPageBreakManual
            
            End If
        Next n
            
    Application.ScreenUpdating = True
End Sub

 

[igold]

Hi jwb1012,

Does this do what you want...

Sub PageBreaks()


    Dim End_Row As Long, n As Long, Ins As String
    Dim Sh As Worksheet
    
    Application.ScreenUpdating = False
    End_Row = ActiveSheet.Range("L" & Rows.Count).End(xlUp).Row
        
    For n = End_Row To 3 Step -1
        Ins = ActiveSheet.Cells(n, "C").Value
            If Ins = "BOE Summary" Then
                ActiveSheet.HPageBreaks.Add Range("A" & n)
            End If
    Next n
            
    Application.ScreenUpdating = True
    
End Sub

HTH

igold