Moving Page Breaks

[SkipVought]

Hi gang,
Excel behaves differently if I run my program in Normal or Page Break View. My objective is to reassign horizontal page breaks at places where my report (1 wide by 99 tall) major groups break in col 1.

It "works" in page break view (except I get a subscript out of range run time error that will continue to completion from that point)

In normal, none of the page breaks ever get inserted.

Any thoughts?

Sub ReassignPageBreaks()
    Dim pb As HPageBreak
    PgSetup
    ActiveSheet.ResetAllPageBreaks
    For Each pb In ActiveSheet.HPageBreaks
        With pb
            If IsEmpty(Cells(.Location.Row, 1).Value) Then
                Set pb.Location = Rows(Cells(Cells(.Location.Row, 1).End(xlUp).Row, 1).Row)
            End If
        End With
    Next
    Exit Sub
End Sub
Sub PgSetup()
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""Arial,Bold""DAILY JOB REPORT DATABASE" & Chr(10) & "&A" & Chr(10) & "Week Ending: 1/30/2003"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.82)
        .BottomMargin = Application.InchesToPoints(0.38)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = -2
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 99
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

 

[RobBroekhuis]

Skip,
I haven't tried to reproduce your problem. However, it seems to me you could go about this a little differently for a more robust procedure (maybe a little slower, though):
starting at the top of your data, move down the rows; when you encounter a page break, instead of trying to move it, just insert a MANUAL page break at the appropriate point, by the same procedure you're now using to move the automatic one. Actually, you can probably do this and still use the hpagebreaks collection to find the page breaks a little quicker. Am I making sense?
Rob