Hi all. Hoping someone can help. I've put together code to transfer data between sheets. The applicable data from sheet1 will transfer and overwrite the existing data on sheet2 (how I want it). Each line of data on sheet1 has an ActiveX Command Button next to it to trigger the transfer. I want each button to run the same macro, but the Command Button name to change based on the button that was clicked. The part I can't break is changing the CommandButton"#", based on a changing variable. New rows with buttons can be added as needed (generated automatically through vba) and i need those newly generated buttons to operate the same way. They need to operate independently, so a loop is not what I'm looking for. Thanks in advance all. Been pulling my hair out!
VBA Code:
Sub getRow()
Dim CommandButton As Object
Dim r_no As Integer
Set Obj = ActiveSheet.CommandButton1
With Obj.TopLeftCell
r_no = .Row
Sheets("CARDS").Cells(2, 3).Value = Cells(r_no, 6).Value
Sheets("CARDS").Cells(20, 2).Value = Cells(r_no, 22).Value
Sheets("CARDS").Cells(21, 2).Value = Cells(r_no + 1, 22).Value
Sheets("CARDS").Cells(22, 2).Value = Cells(r_no + 2, 22).Value
Sheets("CARDS").Cells(20, 3).Value = Cells(r_no, 23).Value
Sheets("CARDS").Cells(21, 3).Value = Cells(rno + 1, 23).Value
Sheets("CARDS").Cells(22, 3).Value = Cells(r_no + 2, 23).Value
End With
End Sub