VBA Insert Page Break based on cell contents

[Nyanko]

Hi,

I'm looking for a very quick VBA snippet that will insert a pagebreak everytime it finds a row called "Cumulative Total". I've tried searching and can only seem to find long exampled about setting breaks when values change etc...
Many thanks

 

[sheetspread]

Sub addpagebreaksatvalue()
Dim x As Integer
Dim LR As Integer
LR = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To LR
If Cells(x, 1).Value = "Cumulative Total" Then
ActiveSheet.HPageBreaks.Add Before:=Rows(x + 1)
End If
Next
End Sub

You can use find instead of the loop also

 

[montecarlo2079]

Sub pagebreak()
Dim MyCell As Range
For Each MyCell In Range("A1:A10975")

If MyCell.Value Like "*Page 1 of 1*" Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add ActiveCell.Offset(1, 0)
Else
End If

ActiveCell.Offset(1, 0).Select
Next
End Sub

 

[Johngeology]

I really dont know what im doing (obviously).

Could someone please tell me how to manually add a page break before the activesheet.paste line? its seems it should be simple, but i really dont understand the couple of lines BEFORE the activesheet.paste command. I think its looking for the last blank row or cell someway.

Many Thanks in advance.



Sheets("Summary").Select
NextRow = Cells(Rows.Count, 2).End(xlUp).Row + 2
Cells(NextRow, 2).Select
ActiveSheet.Paste
Range("B3").Select

 

[sheetspread]

You don't need the .select or activesheet here:

NextRow = sheets("Summary").Cells(Rows.Count, 2).End(xlUp).Row + 2
sheets("Summary").Cells(NextRow, 2).paste

does the same thing, then you can:

sheets("Summary").HPageBreaks.Add Before:=NextRow +1 or wherever.

 

[sheetspread]

I mean

sheets("Summary").HPageBreaks.Add Before:=rows(NextRow +1)

 

[Johngeology]

Thank you!