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

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 befo=c.Offset(1, 1)
Next c
End With


End Sub


--
Hope that helps.

Vergel Adriano


"TDR" wrote:

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

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 :))


"Vergel Adriano" wrote:

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 befo=c.Offset(1, 1)
Next c
End With


End Sub


--
Hope that helps.

Vergel Adriano


"TDR" wrote:

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

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 befo=c.Offset(1, 1)
End If
Next c
End If
End With


End Sub



--
Hope that helps.

Vergel Adriano


"TDR" wrote:

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 :))


"Vergel Adriano" wrote:

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 befo=c.Offset(1, 1)
Next c
End With


End Sub


--
Hope that helps.

Vergel Adriano


"TDR" wrote:

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

Thank you very much!

Take care

"Vergel Adriano" wrote:

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 befo=c.Offset(1, 1)
End If
Next c
End If
End With


End Sub



--
Hope that helps.

Vergel Adriano


"TDR" wrote:

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 :))


"Vergel Adriano" wrote:

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 befo=c.Offset(1, 1)
Next c
End With


End Sub


--
Hope that helps.

Vergel Adriano


"TDR" wrote:

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