Add button to copy Excel rows

Question

I want to add a button on a row in a spreadsheet that copies the contents from one cell to another if and when I click the button. I already have found the code to do this, however, I want to be able to copy the button to a new row and have it work on that row, with the cells on that row, without having to go in and change the code.

This code below is the one I have, but it is specific to the one row it is written for only:

Sub CopyValue()

    ActiveSheet.Range("O7").Value = ActiveSheet.Range("K7")
    ActiveSheet.Range("R7").Value = ActiveSheet.Range("N7")

End Sub

and copies K7 cell contents to O7 cell contents etc.

If I copy the button o the next row it doesn't work for that row unless I go in and change the code, which means adding a new button or macro name and code to do so.

I want to just copy and add a row below again and again, and have it work on that row. Cell formulas work this way, but how do I trigger a cell formula with a click of a button on the same row?

I need to be able to do this on hundreds or more rows, just by copying and pasting, or duplicating the row, or copying and pasting the button to a new row.

I have Excel 2007.

Answer 1

You can use the following VBA code to accomplish your goal much more easily than generating code for individual ActiveX or Form Control buttons.

The following VBA allows the user to double-click a cell in a designated column (the "button column") and the code will copy the values on the same row the user double-clicked from the source to destination columns you specify. This code works on all rows in your worksheet without requiring you to modify the code in any way.

Don't be thrown off by the code's repeated references to cells on row 1 (e.g. O1 and K1). In each of these cases, the .Column property is used because we're only interested in the column in question. The row that will be manipulated is always the same row the user double-clicks.

In the Excel VBA Editor (press Alt+F11 to open) navigate to your worksheet's module, then paste in the following code:

Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo Quit
'Copy values between columns on same row that user double-clicks

'Quit if Target range includes more than one cell
If Target.Count > 1 Then Exit Sub

With ActiveSheet

    'Here, specify the worksheet column the user can double-click to activate this
    'routine.  For the "B" column, in the Range method specify cell "B1" (all cells
    'in column will be used, not just that on row 1).
    If Target.Column = .Range("B1").Column Then
        .Cells(Target.Row, .Range("O1").Column) = .Cells(Target.Row, .Range("K1").Column)
        .Cells(Target.Row, .Range("R1").Column) = .Cells(Target.Row, .Range("N1").Column)

        Cancel = True   'Cancel the double-click
    End If

    'Here you can specify a second "button column" with its own set of copy actions.
    If Target.Column = .Range("C1").Column Then
        .Cells(Target.Row, .Range("E1").Column) = .Cells(Target.Row, .Range("F1").Column)
        .Cells(Target.Row, .Range("G1").Column) = .Cells(Target.Row, .Range("H1").Column)

        Cancel = True   'Cancel the double-click
    End If

End With

Quit:
End Sub

As written, the code will be triggered when the user double-clicks any cell in column B. To change this, modify this line of code to point to the column you wish to act as your "button column":

If Target.Column <> .Range("B1").Column Then Exit Sub

The rest of the code should be self-explanatory, but ask any questions you have.

Edit: I changed the VBA to permit two button columns, each able to copy values between different source and destination columns.