Unable to insert Pagebreak before row 1108 !!

[Filips Benoit]

Dear All,

Before printing ( using code under commandbutton) the code inserts
pagebreaks before each chapter ( column 1 not empty).
Works fine about 1 year !
Suddenly in 1 worksheet before row 1108 it is not possible to insert a
pagebreak, Not by code and NOT manualy.
The code triggers error 1004 Application-defined or objecdefined error.

Do YOU know a possible cause for this behavior.


If bSetPagebracksChapters = True Then
With ActiveSheet
.ResetAllPageBreaks
iLastSourceRow = .Cells(Rows.Count, "A").End(xlUp).Row
'Search for the last row with data in Column A
For iRow_Index = 2 To iLastSourceRow Step 1
If IsEmpty(.Cells(iRow_Index, 1).Value) Then
Else
.HPageBreaks.Add Before:=.Cells(iRow_Index, 1)
<<<< This line triggers error 1004 on row 1108 only !!!!!
End If
Next
End With
End If

Thanks,

Filip

 

[Barb Reinhardt]

I tried your code and got this message

Run-time error '1004'

The action exceeds the number of page breaks you can manually add to a
worksheet. A worksheet can contain up to 1026 horizontal page breaks.

Do you really want that many page breaks???

 

[Filips Benoit]

Hey,

1. observation:

iHorizPageBreakCount = .HPageBreaks.Count

Inserting the codeline above to show the numbre of Horizontal PageBreaks
when the error occurs shows that
in my case the error 1004 occures in row 1108 and iHorizPageBreakCount = 20
!!

2. observation:

The problem is solved if I delete row 1108 and insert a new row just
like is was before.
Then - no error - a msgbox at the end of the routine shows that
iHorizPageBreakCount = 31 !!


Filip

 

[Filips Benoit]

Dear All,

Problem: The code could not isert a HPageBreak in the active PrintArea !!!

I added 1 codeline to clear the PrintArea
ActiveSheet.PageSetup.PrintArea = ""

Filip