Steven,

Thanks for your tip. The fact that it inserts a row above the selected cell
is very usefull, but there is still something else I need. I want to insert
not just a white row, but a row with specific formulas that I already created
at the bottom of the sheet (e.g. on row 100). So it has to insert row 100
above the selected cell. If the macro is executed once, this means that the
next time it should insert row 101 as it shifted one row down due the
insertion. Is this possible? Thanks

" wrote:

On Sep 24, 10:29 pm, Tom82 wrote:
Hi, I created easy macro below to enter a predesigned (formats and formulas)
row in a protected sheet and then protect it again. Now I would like to set
the macro that it will insert the line above the cell in which I'm standing
at the time that I activate the Macro. Furthermore, I would like to fix the
row that I enter.... in this case it is row 65..but when I execute the Macro
ones, it will become row 66... Can somebody tell me which changes I have to
make in the codes?Thanks

ActiveSheet.Unprotect
ActiveWindow.SmallScroll Down:=21
Rows("65:65").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-21
Rows("20:20").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Hello Tom,

I assume what you want is to keep the row you have selected as the
'active' row, to return to after you have inserted a row in the range
above. You use Cut/Copy - do you want the selected row cut and
inserted somewhere else, or were you just using that as a method to
insert a row? The following code is untested (can't run it on my Mac
at home) but should insert a row before the currently selected cell,
then select the row again.

Steven


Sub InsRw()
Dim Rng as Range
Dim Sht as Worksheet

Set Sht = ActiveSheet
Set Rng = ActiveCell.EntireRow

Sht.Unprotect
Rng.Insert Shift:=xlDown
Set Rng = Rng.Offset(1,0)
Sht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub