PDA

View Full Version : [SOLVED] Moving automatically generated page breaks



froggyface88
01-04-2014, 09:34 AM
I have a document without a predetermined number of rows that I want to include on each page. I can manually adjust the page break settings to include the right number of rows per page, but I would like to do this as part of my code. Right now, my code is adding an extra page break in the correct place but isn't moving the preset page break. Help? Thanks!



NoGames = 0
Do Until NoGames = 5
If Cells(iRow, 1).Value = "Name" Then
Cells(iRow, 1).Select
NoGames = NoGames + 1
Games = Games + 1
iRow = iRow + 1
Else: iRow = iRow + 1
End If
Loop
iRow = iRow - 2
Games = Games - 1
Rows(iRow).PageBreak = xlPageBreakManual
Loop

westconn1
01-04-2014, 02:50 PM
you have to move each page break individually,

s.HPageBreaks(1).Location.Row = irowwhere s is a worksheet object
can use a loop

for i = 1 to s.HPageBreaks.Count
s.HPageBreaks(i).Location.Row = irow * i
next

froggyface88
01-05-2014, 04:02 PM
This ended up by working nicely. I had to make sure the view was set to pagebreak mode. Thanks for your help!

NoGames = 0
Do Until NoGames = 5
If Cells(iRow, 1).Value = "Name" Then
Cells(iRow, 1).Select
NoGames = NoGames + 1
Games = Games + 1
iRow = iRow + 1
Else: iRow = iRow + 1
End If
Loop
k = k + 1
iRow = iRow - 2
Games = Games - 1
Rows(iRow).PageBreak = xlPageBreakManual
Set ActiveSheet.HPageBreaks(k).Location = ActiveSheet.Cells(iRow, 1)