Macro to run in same row as selected button

G

Guest

Hi, below I have posted my code which is designed to insert a copied row in
whichever row a button is when it is clicked. What am I doing wrong? it seems
to get as far as the 'Cells(MyRowToUse, 1).Select' line of code.

I am a beginer so suspect it may be something simple but help would be
appreciated.

N

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

'
Dim BTN As Button
Dim MyRowToUse As Variant


MyRowToUse = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address
'.row, .column


MsgBox MyRowToUse



Sheets("Template").Select
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
 
T

Tom Ogilvy

myrowtouse will have a value like B21


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

'
Dim BTN As Button
Dim MyRowToUse As Variant
MyRowToUse = ActiveSheet.Buttons(Application.Caller).TopLeftCell.row
'.row, .column
MsgBox MyRowToUse
Sheets("Template").Rows("11:11").EntireRow.Copy
Sheets("Risk Register").Cells( _
MyRowToUse, 1).Insert Shift:=xlShiftDown
End Sub

This shifts you button down.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy into Row holding Button 2
Can Vba workout if a selection is part of a group? 0
macro 2
How can I indicate in a macro to which last sheet to return ? 9
Copying a row and pasting the same below it with just formulas 3
Select a specific row in named range 7
Create Detail Sheets from Pivot Table 1
Loop to copy paste rows base on table arrays 0

Top