VBA Code - Insert Pagebreak in Selected Cells (Range)

[EdwardOcampo]

Hi!

I'm trying to use this code to a range of cells selected. Any input will be appreciated

Sub InsertPageBreaks()
'Updateby20140618
Dim xLastrow As Long
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
xRow = Application.InputBox("Row", xTitleId, "", Type:=1)
xLastrow = xWs.Range("A15").SpecialCells(xlCellTypeLastCell).Row
For i = xRow + 1 To xLastrow Step xRow
    xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)
Next i
End Sub

[dangelor]

Don't quite know what it is you're asking. Your code (with a few subtle corrections) will insert a page break every xRow count of rows. Is that what you want to do?

[mana]

Option Explicit


Sub test()
    Dim xWs As Worksheet
    Dim sel As Range
    Dim xLastrow As Long
    Dim xRow As Long
    Dim i As Long
    
    Set xWs = ActiveSheet
    Set sel = Selection
  
    xRow = Application.InputBox("Row", Type:=1)
    If xRow < 1 Then Exit Sub
    
    xLastrow = sel.Cells(sel.Count).Row
    
    xWs.ResetAllPageBreaks
    
    For i = xRow + sel.Row To xLastrow Step xRow
        xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)
    Next i
    
End Sub

[EdwardOcampo]

yes! However not to the entire sheet, only add page breaks to the selected range of cells. Rework.jpg
For example, in reference to the attached sheet, selecting A15:K27.

[EdwardOcampo]

Option Explicit


Sub test()
    Dim xWs As Worksheet
    Dim sel As Range
    Dim xLastrow As Long
    Dim xRow As Long
    Dim i As Long
    
    Set xWs = ActiveSheet
    Set sel = Selection
  
    xRow = Application.InputBox("Row", Type:=1)
    If xRow < 1 Then Exit Sub
    
    xLastrow = sel.Cells(sel.Count).Row
    
    xWs.ResetAllPageBreaks
    
    For i = xRow + sel.Row To xLastrow Step xRow
        xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)
    Next i
    
End Sub

You are the best.

Thank you so much. I worked great. I removed the resetallpagebreak command and it did the trick.