PDA

View Full Version : VBA Code - Insert Pagebreak in Selected Cells (Range)



EdwardOcampo
02-24-2017, 01:16 PM
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
02-24-2017, 03:48 PM
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
02-25-2017, 11:03 PM
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
02-27-2017, 08:20 AM
yes! However not to the entire sheet, only add page breaks to the selected range of cells. 18497
For example, in reference to the attached sheet, selecting A15:K27.

EdwardOcampo
03-15-2017, 12:25 PM
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.