Use One Excel VBA Macro for Multiple Form Buttons
Question details
Use one VBA macro for multiple worksheet buttons so each button copies data from its own row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains several buttons, each associated with a different row of data.
- Observed behavior
- The shared macro should identify the clicked button and use its position to select the corresponding row data.
Make sure the buttons are worksheet shapes or Form Control buttons, not ActiveX controls or UserForm buttons. Enable the Developer tab if needed and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
Identify the clicked button with Application.Caller
Assign the same macro to each worksheet button and use its top-left cell to find the data to copy.
Application.Caller returns the name of the clicked shape or Form Control button. TopLeftCell identifies the cell beneath its upper-left corner, and Offset selects a cell relative to that position.
The example copies the cell immediately to the right of each button's top-left cell to the next row in column A on Sheet2. It assumes Sheet2 has a header in A1.
Create or rename a worksheet Sheet2 and enter a header in cell A1. Place each source button so its upper-left corner is in the intended row.
Open Developer > Visual Basic, choose Insert > Module, and paste this code: Sub CopyFromButtonRow() Dim rng As Range Dim dest As Worksheet Set rng = ActiveSheet.Shapes(Application.Caller).TopLeftCell Set dest = ThisWorkbook.Worksheets("Sheet2") rng.Offset(0, 1).Copy Destination:=dest.Cells(dest.Rows.Count, "A").End(xlUp).Offset(1, 0) End Sub
Right-click each worksheet shape or Form Control button, select Assign Macro, choose CopyFromButtonRow, and click OK.
Click a button on the worksheet and check column A on Sheet2. Adjust rng.Offset(0, 1) in the VBA editor if the source data is in a different position relative to the button.

Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheet files. Its Microsoft Office format compatibility helps you move existing files into WPS; test macro-dependent workbooks before relying on the same automation.

Frequently Asked Questions
How does the macro know which button was clicked?
When you click an assigned worksheet shape or Form Control button, Application.Caller returns its name. ActiveSheet.Shapes(Application.Caller).TopLeftCell then returns the cell beneath the button's upper-left corner.
Why does Excel say the item with the specified name was not found?
The caller may not be a shape on the active worksheet. Click the assigned worksheet button to run the macro, check that the button is on the active sheet, and confirm it is a shape or Form Control rather than an ActiveX control.
Can I use this exact macro with an ActiveX or UserForm button?
No. The Application.Caller shape lookup shown here is intended for worksheet shapes and Form Control buttons. ActiveX and UserForm buttons use their own event procedures and need a different way to identify the control and source data.
How do I copy a different cell from the button's row?
In Developer > Visual Basic, change rng.Offset(0, 1) to the offset you need. For example, rng.Offset(0, 2) selects the cell two columns to the right of the button's top-left cell.
Why does a button copy data from the wrong row?
TopLeftCell uses the cell beneath the button's upper-left corner. Move or resize the button so that corner sits in the intended row, then click the button again.




