Excel VBA Add New Line Script Not Adding Row Based On Where The Button Is Been Pressed

Question

I have a excel spread sheet where I have added a button on a row which I wanted to click, take a copy of that row and then duplicate underneath.

For some reason it does not seem to be selecting the row where I click the button but where ever the cursor is meaning the wrong line is copied.

Is there any way I can change the below to make the active cell where ever the button has just been pressed ?

Sub InsertRows()
Dim x As Integer
  x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
  If x = False Then Exit Sub
    ActiveCell.EntireRow.Copy
    Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

Many thanks in advance

Terran

EDIT

My final solution with Thomas' help.

The slight difference is that this allows the button its self to also be copied as well as the row.

Sub InsertRows()


    Dim iRow As Integer
    Dim x As Integer
    Dim i As Integer

    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
    iRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    If x = False Then Exit Sub

    For i = 0 To (x - 1)


    'Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown


    Range("A" & iRow).EntireRow.Copy
    Range("A" & iRow + 1 + i).EntireRow.Insert Shift:=xlDown

    Next i
    Application.CutCopyMode = False

End Sub

Clicking the button doesn't change the ActiveCell. You can get which button was pressed by looking at Application.Caller — Vincent G
– Vincent G, Commented Oct 4, 2017 at 10:05

Thomas G · Accepted Answer · 2017-10-04 11:30:47Z

1

Use this to get the row of the button you have just clicked:

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

I don't really understand what you are trying to do with your msgbox collecting an offset. So here's the code to do excatly what you asked, ie copy the row where the button is, on the row right underneath:

Sub Button1_Click()

    Dim iRow As Integer
    iRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    Range("A" & iRow).EntireRow.Copy
    Range("A" & iRow + 1).PasteSpecial

End Sub

EDIT: If you want to copy it several times just do this :

Sub Button1_Click()

    Dim iRow As Integer
    Dim x As Integer
    Dim i As Integer

    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
    iRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

    For i = 0 To (x - 1)

        Range("A" & iRow).EntireRow.Copy
        Range("A" & iRow + 1 + i).PasteSpecial

    Next i

End Sub

edited Oct 4, 2017 at 11:30

answered Oct 4, 2017 at 10:05

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Terran Brown Over a year ago

Hi - so I've tried to pull your suggestion in to my code but I seem to be having a seniuor moment. The following does seem to work but does not copy any thing in the end. I needed in effect to select the row I want to copy and copy x amount of times based on user in put below. So while your code @Thomas G does work it only allows one row at a time.

Terran Brown Over a year ago

Sub InsertRows() Dim x As Integer Dim iRow As Integer x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1) If x = False Then Exit Sub iRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row ActiveCell.EntireRow.Copy Range(ActiveCell, ActiveCell.Offset(x - 1, iRow)).EntireRow.Insert Shift:=xlDown Application.CutCopyMode = False End Sub

Thomas G Over a year ago

@TerranBrown See my adapted answer. It wasnt that hard, was it?

Terran Brown Over a year ago

Hi Thomas - your right... off to give my self a beating :) Thanks for your help. Terran

Collectives™ on Stack Overflow

Excel VBA Add New Line Script Not Adding Row Based On Where The Button Is Been Pressed

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related