page break help

[000]

Is there any code out there that could help me insert page breaks into a bill of materials? Perhaps it could insert a page break every 70 rows and be able to determine where the last row is that has text in it and insert a page break there.

Thanks for the help!!

 

[CharlesChuckieCharles]

here's something from the help file

Range.PageBreak Property

Returns or sets the location of a page break. Can be one of the following XlPageBreak constants: xlPageBreakAutomatic, xlPageBreakManual, or xlPageBreakNone. Read/write Long. Syntax
expression.PageBreak
expression A variable that represents a Range object.

Remarks
This property can return the location of either automatic or manual page breaks, but it can only set the location of manual breaks (it can only be set to xlPageBreakManual or xlPageBreakNone).
To remove all manual page breaks on a worksheet, set <CODE>Cells.PageBreak</CODE> to xlPageBreakNone.


Example
This example sets a manual page break above row 25 on Sheet1.

<TABLE><TBODY><TR><TH>Visual Basic for Applications</TH></TR><TR><TD>

<CODE>Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual</CODE>
</PRE></TD></TR></TBODY></TABLE></P>This example sets a manual page break to the left of column J on Sheet1.

<TABLE><TBODY><TR><TH>Visual Basic for Applications</TH></TR><TR><TD>

<CODE>Worksheets("Sheet1").Columns("J").PageBreak = xlPageBreakManual</CODE>
</PRE></TD></TR></TBODY></TABLE></P>This example deletes the two page breaks that were set in the preceding examples.

<TABLE><TBODY><TR><TH>Visual Basic for Applications</TH></TR><TR><TD>

<CODE>Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakNoneWorksheets("Sheet1").Columns("J").PageBreak = xlNone</CODE>
</PRE></TD></TR></TBODY></TABLE></P>Obviously its VBA

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
For i = 1 To LR Step 70
    ActiveSheet.HPageBreaks.Add Before:=Range("A" & i)
Next i
ActiveSheet.HPageBreaks.Add Before:=Range("A" & LR + 1)
End Sub

 

[000]

That code did not work. It has a run time error '1004':
Application-defined or object defined error

It had a problem with this line:
ActiveSheet.HPageBreaks.Add Before:=Range("A" & LR + 1)

 

[VoG]

Sorry, try

Sub pagebreaktest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR Step 70
    ActiveSheet.HPageBreaks.Add Before:=Range("A" & i)
Next i
ActiveSheet.HPageBreaks.Add Before:=Range("A" & LR + 1)
End Sub

 

[000]

ActiveSheet.HPageBreaks.Add Before:=Range("A" & i)

Now this is the problem. Any thoughts? Thanks again!!

 

[VoG]

Testes and working

Sub pagebreaktest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR Step 70
    ActiveSheet.PageSetup.HPageBreaks.Add Before:=Range("A" & i)
Next i
ActiveSheet.HPageBreak.Add Before:=Range("A" & LR + 1)
End Sub

 

[CharlesChuckieCharles]

Testes and working

Sub pagebreaktest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR Step 70
    ActiveSheet.PageSetup.HPageBreaks.Add Before:=Range("A" & i)
Next i
ActiveSheet.HPageBreak.Add Before:=Range("A" & LR + 1)
End Sub

RED lines are different

Sub pagebreaktest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR Step 70
    ActiveSheet.PageSetup.HPageBreaks.Add Before:=Range("A" & i)
Next i
ActiveSheet.HPageBreak.Add Before:=Range("A" & LR + 1)
End Sub

 

[000]

The program is still having problems with the red lines. I really am not sure how to fix this.