Pagebreak problems

[RHemmings]

With the code below, the only way can get the pagebreaks to work is to include the Rows("16:16&quot.Select line. However, I need to have the pagebreak be variable. If I comment out the Rows line and depend on the Cells(z, 1).Select line, I get an error "Runtime error '1004', Application-defined or Object-defined error" on the PageBreak line.

Can some help steer me in the right direction?

Thanks,
Bob

With Selection.CurrentRegion
rc = .Rows.Count
End With
rc = rc + 50
For z = 1 To rc
If Cells(z, 4).Value = "Initial Balance" Then
Cells(z, 1).Select
Rows("16:16&quot.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Selection.EntireRow.Insert Shift:=xlDown
Selection.EntireRow.Insert Shift:=xlDown
z = z + 2
End If
Next

 

[SkipVought]

Hi,

Try this...

    rc = Cells(1, 1).CurrentRegion.Rows.Count + 50
    For z = 1 To rc
        If Cells(z, 4).Value = "Initial Balance" Then
          ActiveSheet.HPageBreaks.Add before:=Cells(z, 1)
          Range(Cells(z, 4), Cells(z + 1, 4)).EntireRow.Insert shift:=xlUp
          z = z + 2
        End If
    Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RHemmings]

Skip,

Thank you. You led me in the right direction. One of my problems was that the first record was a "initial Balance" record and the code was set up so that a pagebreak was the first thing that was requested. It couldn't do it because it was already at a page break (I think). At any rate it now works.

Thanks again,
Bob