Create a Macro Button in Excel to Copy Cells
Joshua earned an MBA from USF and he writes mostly about software and technology.
How to automate copying a cell or multiple cells with the option to display a message box of what is copied.
Display a Message Box
To avoid errors or to increase your speed in Excel, you may want to create a button that allows you the ability copy a cell or a range of cells. Here I explain how to automate copying a cell or multiple cells with the option to display a message box of what is copied.
We'll start with locating the developer tab so that a button can be created.
Display the Developer Tab
For machines using Windows, you can learn how to display the developer tab with the following link here.
To display the developer tab on a Mac, click the Excel tab on the menu. Then, click on preferences.
Next, click on the view button.
Click the check box beside developer tab in the in ribbon section at the bottom.
Adding a Command Button
For Windows machines, go the developer table and click on the insert option. Click on the command button then trace the size of the button you want on the worksheet.
For a Mac, go to the developer tab and click on the button icon. Next, click on any cell in the spreadsheet. Name the macro with no space and select the new button.
Running the Macro
A module will appear in a new window. Copy the below code into that module and replace the name after sub with the name created before clicking new in the assign macro window.
Sub copycell() Range("A1").Select Selection.Copy End Sub
You can now copy the cell stated in the macro that is assigned to the button by clicking on the button. If you have a range or more than one cell to copy, simply replace the cell reference with a range reference like the one in the code below.
Sub copycell() Range("A1:A2").Select Selection.Copy End Sub
The button name can be edited in Windows by right clicking on it or by holding control + click on a Mac followed by clicking on the button text.
Show a Message Box of Copied Values
If you would like a message box to appear with the copied cell value(s) another line of code is require. Usually when I use a copy button the contents of the cell is created with a dynamic formula and hidden. In this situation, I would like to review what has been copied to the clipboard with a message box.
The new line of line code is shown in the macro below that that will display a message box. Replace the sheet name and the range that you would like to appear in your own message box. Additionally, at the end of the code you can give the message box a custom title.
Sub copycell() Range("A1:A2").Select Selection.Copy MsgBox Worksheets("sheet1").Range("A1"), ,"Copied" End Sub
Message Box Tips
Instead of using a cell reference for the message box body, you may use a text string.
MsgBox "Content was copied", , "Copied"
A cell reference can also be used in the title of the text box.
MsgBox "This Box", , Worksheets("sheet1").Range("A1")
The ampersand symbol can be used to add spaces and join additional strings. This also works with cell references.
MsgBox "This Box" & " " & " and That Box", , "Title"
To execute multiple lines of text use vbNewLine with ampersands before and after between your cell references and text strings.
& vbNewLine &
Saving this Workbook
After creating your macros be sure to save the workbook in the form of an .xlsm file. If this is not completed that macro buttons will not work the next time the workbook is open.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2022 Joshua Crowder
