inserting page-breaks

[Guest]

hello,

i have an worksheet with data that is exported from an accounting program.
the spreadsheet has an amount every 4,5,6 or 7 lines in column "F". i would
like to have a macro that searches each cell from 1F to the end of the
worksheet and where this is a number in 'x'F i want a page-break inserted
after 'x'.

thanks for any help you can provide

 

[Guest]

TDR,

Here's one way:

Sub test()

Dim c As Range

With ActiveSheet
For Each c In Intersect(.UsedRange,
..Range("F:F")).SpecialCells(xlCellTypeConstants)
.HPageBreaks.Add before:=c.Offset(1, 1)
Next c
End With


End Sub

 

[Guest]

thank you for such a quick reply!!

i'm getting this error - Object variable or With block variable not set

i've found it in the help file so i'll try to figure it out from here (i
think i'll be coming back for the answer though )

 

[Guest]

Hi,

try it this way:

Sub test()

Dim c As Range
Dim rng As Range

With ActiveSheet
Set rng = Intersect(.UsedRange, .Range("F:F"))
If Not rng Is Nothing Then
For Each c In rng
If c.Value > 0 Then
.HPageBreaks.Add before:=c.Offset(1, 1)
End If
Next c
End If
End With


End Sub

 

[Guest]

Thank you very much!

Take care

Hi,

try it this way:

Sub test()

Dim c As Range
Dim rng As Range

With ActiveSheet
Set rng = Intersect(.UsedRange, .Range("F:F"))
If Not rng Is Nothing Then
For Each c In rng
If c.Value > 0 Then
.HPageBreaks.Add before:=c.Offset(1, 1)
End If
Next c
End If
End With


End Sub