Copy into Row holding Button

[nejlangton]

On my worksheet I have a button that inserts a copied row from anothe
sheet when pressed. The code is as follows:

Sub NEWRMO()
'
' NEWRMO Macro
' Macro recorded 02/09/2004 by charles
'

'
Sheets("Template").Select
Rows("11:11").EntireRow.Copy
Sheets("Risk Register").Select
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub


At present the row is pasted wherever the active cell is, however
would like the macro to insert the row on the same line as the butto
was when pressed.

Presumably I need to get the macro to select the row relative to th
button that operates it?

Any ideas?

One other issue might be that the button is itself copied several time
and so exists several times on the same sheet. Perhaps a bette
explanation is to explain what the sheet does. It is a Risk register t
record project risks. There exists a macro to add a new risk (which i
two rows of data, the second being a row with a mitigation option), a
well as the two rows of data it also copies the button as describe
above. This button activates the macro printed above and insert
another mitigation option. Dont know if this paragraph helps if no
please disregard

 

[GJones]

Hi nejlangton;



Sub NEWRMO2()
'
' NEWRMO Macro
' Macro recorded 02/09/2004 by charles
'

'
Sheets("Template").Select
MyRowToUse = 11
Rows("11:11").EntireRow.Copy
Sheets("Risk Register").Select
Cells(MyRowToUse, 1).Select
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub

Thanks,

Greg

 

[nejlangton]

Thanks, but that means that the row is always inserted on row 11. I wan
it to insert on the same row as the button was when pressed, regardles
of where that button was positioned because it will move down as row
are inserted.

i.e. perhaps something like myrowtouse = the same row the button i
when pressed.

Is there anyway of doing this?

Nejl

Hi nejlangton;

Sub NEWRMO2()
'
' NEWRMO Macro
' Macro recorded 02/09/2004 by charles
'

'
Sheets("Template").Select
MyRowToUse = 11
Rows("11:11").EntireRow.Copy
Sheets("Risk Register").Select
Cells(MyRowToUse, 1).Select
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub

Thanks,

Gre