Create a Macro Button in Excel to Copy Cells

Author:
Joshua Crowder
Updated date:
Nov 10, 2022

Joshua earned an MBA from USF and writes mostly about software and technology.

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 to 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 folks 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 the developer tab in the ribbon section at the bottom.

Adding a Command Button

For Windows machines, go to 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 required. 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 a .xlsm file. If this is not completed the 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.