VBA Print Page Breaks

[alfordtp]

I have a sheet that shows account summaries for clients that are 5-10 rows long. When I print out the summary, I cannot use the "fit to x pages tall" method because I cant guarantee that it will not split in the middle of an account summary. I want to find the last full account summary that fits on the page, then create a page break right after that, then continue to do that for all pages.

The header cell for each account summary starts with the word "account" in it. I can filter/find those cell row numbers. I'm not sure how to set the page break, then check again, then set the second page break, then check again...

Any help is appreciated!!

 

[DanteAmor]

Try this

Sub set_print_page_breaks()
  Dim f As Range, r As Range, cell As String
  Set r = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
  Set f = r.Find("account", , xlValues, xlWhole)
  ActiveSheet.ResetAllPageBreaks
  ActiveSheet.DisplayPageBreaks = False
  If Not f Is Nothing Then
    cell = f.Address
    Do
      ActiveSheet.HPageBreaks.Add Before:=f
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

 

[alfordtp]

not sure what the "Find" function is supposed to do in this situation. there will be 10+ accounts on this summary worksheet. I dont want each account to have their own page. I only want to prevent the page break from split an account block into two pages.

 

[DanteAmor]

How many rows per page is ideal?