Tuesday, 17 September 2013

How to copy from a list of items using multiple command buttons in Excel?

How to copy from a list of items using multiple command buttons in Excel?

I am looking to build an Excel worksheet that has a list of items in
column A, each paired with a command button in column B. Ideally it would
look like this:

When pressing a button, the value in the adjacent column A would be copied
to a different worksheet. I understand this is possible to do with a
CommandButton_Click() sub, but I will have around 200 buttons and from my
understanding, I'd have to do a different sub for each one. Is there a way
to avoid this? Is there some code that would apply to all buttons and if a
button is pressed it would copy the adjacent column? An example of my
current sub is:
Sub CommandButton_Click()
Dim rs As Integer
rs = ActiveSheet.Buttons(1).TopLeftCell.Row
Worksheets("Sheet1").Range("A" & rs).Copy _
Worksheets("Sheet2").Range("A" & rs)
End Sub
Hopefully you guys understand what I'm trying to do here. Any help is
appreciated!

No comments:

Post a Comment