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

 

[CalcSux78]

Try:

'Replace this line: 
' <!--StartFragment-->
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(0)
'with
Rows(c.Offset(-1).Row).PageBreak = -4135

 

[lmmay]

I get a "Run Time Error 1004" on that line also.

 

[CalcSux78]

So sorry, I think I had an error in my offset syntax.

Try:

Rows(c.Row + 1).PageBreak = -4135

 

[shg]

Looks to me like your original code should work fine. What happens?

You might want to do a ResetAllPageBreaks first.

 

[lmmay]

Same error!

 

[lmmay]

I get a "Run Time Error 1004
Application-defined or Objectdefined error on line:
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(0)

At what position should I try a reset?

 

[shg]

Try this:

Sub Set_Print_Area()
  Dim wks           As Worksheet
  Dim cell          As Range
  Dim sAdr          As String

  Set wks = ActiveSheet
  With wks
    .Unprotect Password:="XXXXXXXe"  ' you sure this is correct?

    .ResetAllPageBreaks
    Set cell = Range("B:B").Find(What:="APPLICANT NAME:", LookIn:=xlValues, _
                                 LookAt:=xlWhole, MatchCase:=True)
    If Not cell Is Nothing Then
      sAdr = cell.Address
      Do
        .HPageBreaks.Add Before:=cell
        Set cell = Range("B:B").FindNext(cell)
      Loop While cell.Address <> sAdr
    End If

    .PrintPreview
  End With
End Sub

 

[lmmay]

I get Error 1004 on:
.HPageBreaks.Add Before:=cell

 

[shg]

Have you verified that the sheet password is correct?