R/T 438 on .HPageBreaks.Add line

[Jim May]

error: Object doesn't support this prop or method... I don't understand..

Public Sub PrintOnly10() 'Rows set (in page setuup) at top to repeat $4:$4

Dim i As Long
Dim MyRange As Range
Dim MyRange1 As Range
Set MyRange = Range("A5").CurrentRegion 'A5 is first row of data
Set MyRange1 = MyRange.Offset(1, 0).Resize(MyRange.Rows.Count - 1, 9)
With MyRange1
For i = 29 To .Rows.Count Step 10
.HPageBreaks.Add .Cells(i, 1) '<< Getting R/T 438
Next i
End With
End Sub

 

[Vergel Adriano]

Jim,

Try replacing this line

..HPageBreaks.Add .Cells(i, 1)

with this:

..Parent.HPageBreaks.Add .Cells(i, 1)

 

[Dave Peterson]

..Hpagebreaks is used with the worksheet--not the range.

With MyRange1
For i = 29 To .Rows.Count Step 10
.Parent.HPageBreaks.Add .Cells(i, 1) '<< Getting R/T 438
Next i
End With

is one way around it.

.Cells(i, 1).PageBreak = xlPageBreakManual

Is another way.

 

[Jim May]

Right on; But why the Parent?

Jim,

Try replacing this line

.HPageBreaks.Add .Cells(i, 1)

with this:

.Parent.HPageBreaks.Add .Cells(i, 1)

 

[Vergel Adriano]

Hi Jim,

Parent because HPageBreaks is a member of the worksheet object, and not the
range. Since you were working with the range object, you needed refer to
it's parent which is the worksheet object.

 

[Jim May]

Thanks Vergel !!

Hi Jim,

Parent because HPageBreaks is a member of the worksheet object, and not the
range. Since you were working with the range object, you needed refer to
it's parent which is the worksheet object.