I have a set of data with several horizontal pagebreaks.
I would like to write a macro which insert 2rows after a pagebreak is
found.

The idea I have is use .Count and if it's true, return the .Location
and Insert row.
But I don't know how to exactly write the codes

Greatly appreciate if someone can help.
Thanks.

amepluie wrote:

I have a set of data with several horizontal pagebreaks.
I would like to write a macro which insert 2rows after a pagebreak is
found.

The idea I have is use .Count and if it's true, return the .Location
and Insert row.
But I don't know how to exactly write the codes

Greatly appreciate if someone can help.
Thanks.


Phillip london UK
The following code works for me

I tested it using the following data

in a new workbook in sheet1
select cell A11 then insert a pagebreak
select cell A20 then insert a pagebreak
select cell A29 then insert a pagebreak
Enter numbers 1 to 30 in range A1:A30

In a standard module enter the following procedure

Sub testHPB()
Dim x As Long
Dim z As Long
Dim Rng As Range
Dim Rng1 As Range
Dim Rng2 As Variant

x = Sheet1.HPageBreaks.Count
If x = 0 Then Exit Sub

For z = 1 To x
With Sheet1
Set Rng = Range(.HPageBreaks(z).Location.Address).Resize(1,
1).EntireRow
Set Rng1 = Range(.HPageBreaks(z).Location.Address).Offset(1). Resize(2,
1).EntireRow
Rng1.Insert
Set Rng2 = Rng.Offset(2).EntireRow
Rng.Copy (Rng2)
Rng.ClearContents
End With
Next

End Sub

Thanks Martin and Phillip. Really appreciate yourhelp.

Both codes work! Thanks so much! Martin's code only insert one row
though.

I do have another question with the codes by the way:

The code seems to run until the limit of the HPageBreak is reach
(around 1000).
Is there someway to reduce that so macro will stop when there's no more
entry to cell?

Thanks once again!!!!
Ame