Insert page break "after" condition met?

[lmmay]

I am trying to use the following to print a multiple page worksheet.
Can not figure out what my error is.

Sub Set_Print_Area()
'Unprotect Worksheet


ActiveSheet.Unprotect Password:="XXXXXXXe"


'Set Print and Review


    Dim c As Range
    Dim FirstAddress As String
   
    Set c = Columns("B").Find(What:="APPLICANT NAME:", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
    If Not c Is Nothing Then
        FirstAddress = c.Address
        Do
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(0)
            Set c = Columns("B").FindNext(c)
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If
   
   
   x = ActiveSheet.UsedRange.Columns.Count
   Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
   ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
   ActiveSheet.Range("Q2655").End(xlUp).Select
   




ActiveSheet.PrintPreview

 

[lmmay]

Yes, what you are seeing is not the password. I exed it out.

 

[shg]

Have you verified that the worksheet is unprotected?

 

[lmmay]

Yes, it is unprotected.

 

[lmmay]

This is the macro I typically use for printing other forms. However, for this application, the form is 61 rows long and the columns are A -P. To print the scale is 45. The form has a Header and Footer
with the margins set at: Top=1.25; Bottom, Right and Left=0.5; Header & Footer=0.3

This macro allows the top rows to "bleed" into the bottom of Page 2, 3, etc and gets worse on each page. That is why I am trying to break the pages above each instance
of the row that has "Applicant" in the first row of the form.

Is there a better way to accomplish this?

Thanks

Sub Set_Print_Area()
'Unprotect Worksheet

ActiveSheet.UnProtect Password:="reco6700"

'Set Print and Review

Dim x As Long, lastCell As Range
x = ActiveSheet.UsedRange.Columns.Count
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
   ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
ActiveSheet.PrintPreview

'Protect Worksheet
      
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Password:="reco6700"
End Sub

 

[shg]

Can you insert page breaks manually?

 

[lmmay]

Yes. However, most applicants using the form are not educated enough to insert manual page breaks.

 

[shg]

It works fine here. I don't have another suggestion unless you can put the workbook on box or dropbox and post a link.

 

[shg]

You can't set a page break on the first row.

If cell.Row > 1 Then .HPageBreaks.Add Before:=cell

 

[lmmay]

That adds another If statement. Where would this be placed? This replaces the page break statement after the "Do"?

 

[shg]

Replace

.HPageBreaks.Add Before:=cell

with

If cell.Row > 1 Then .HPageBreaks.Add Before:=cell