Hi Stabilo,

How can I change the code so the
rCell is equal to the current selected row (and not the "A15")


Try:

'=====================
Public Sub Tester2()
Dim rCell As Range

Application.ScreenUpdating = True

Set rCell = ActiveCell

rCell(2).Resize(10).EntireRow.Insert shift:=xlDown
rCell.EntireRow.Copy
rCell.Offset(1).Resize(10).EntireRow.PasteSpecial _
Paste:=xlPasteFormats
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
'<,=====================


I have test it but it seems that is not working as expected.
In fact if you run several time the same code you will see that it keeps
adding 10 rows instead of just overwritting the existing new 10 rows.

If code to insert rows is run multiple times, multiple insertions will
normally occur.

If your intention is not to insert any rows but rather, simply to format
the 10 rows after the active cell, then try:

'=======================
Sub Tester02()

ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).Resize(10).EntireRow.PasteSpe cial _
Paste:=xlPasteFormats
Application.CutCopyMode = False
ActiveCell.Select

End Sub
'<<=======================


---
Regards,
Norman



"stabilo" wrote in message
...
Hi Norman,

thanks
a lot for the fast reply. I have test it but it seems that is not working
as
expected.
In fact if you run several time the same code you will see that it keeps
adding 10 rows instead of just overwritting the existing new 10 rows.

Do you have time to have a look ? also How can I change the code so the
rCell is equal to the current selected row (and not the "A15")


"Norman Jones" wrote:

Hi Stabilo,

Try:

'=======================
Public Sub Tester()
Dim rCell As Range

Application.ScreenUpdating = True

Set rCell = Range("A15") '<<=========== CHANGE

rCell(2).Resize(10).EntireRow.Insert shift:=xlUp
rCell.EntireRow.Copy
rCell.Offset(1).Resize(10).PasteSpecial Paste:=xlPasteFormats
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub
'<<=======================


---
Regards,
Norman



"stabilo" wrote in message
...
I need to insert 10 rows after a specifc row (let's call it source row)
and
the new rows have to have the same formating as the source row.

So if I have a cell on row 15 selected, the macro should insert 10 row
below
row 15 with the same formating (eg. border, shading, font,...) wihtout
the
values .

Has it be already done ? thanks