Excel VBA: Copy and Paste Selected Cells with a Macro

To copy selected cells with VBA, call the range’s Copy method and specify a destination cell, as in Selection.Copy Destination:=Range("D5"). The macro below lets you select a range, choose a destination, and copy the cells there while retaining their formulas and formatting.

What copying selected cells does

In Excel, “selected cells” means the current selection, which may be one cell, a rectangular range, or—in some situations—multiple separate areas. A VBA macro can refer to that selection and copy its contents to another location. The key decision is what “copy” should include: a normal range copy transfers more than just the displayed text.

The example in this article is for desktop Excel for Microsoft 365 on Windows. It copies one contiguous range to a destination in the same workbook. In a typical copy, Excel transfers cell contents, formulas, formatting, and other cell attributes such as number formats. Relative references in formulas can adjust to the new location, just as they do when copying cells manually.

That behavior is useful when reproducing a block of a worksheet, but it may not be right for every task. If you need only the calculated results, use a values-only approach instead. If you need a picture of the cells or a link to the original data, those are different operations and require different code or paste options.

Use a macro to choose the destination

This macro checks that the current selection is a range, declines noncontiguous selections, and asks you to identify the upper-left destination cell. It copies the whole selected block starting at that cell. For example, if you select B2:C4 and choose F2, the copied block occupies F2:G4.

Sub CopySelectedCellsToDestination()
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' A chart or other object is not a worksheet cell range.
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select worksheet cells before running this macro."
        Exit Sub
    End If

    Set sourceRange = Selection

    ' Copying separate areas together can be ambiguous; use one block.
    If sourceRange.Areas.Count > 1 Then
        MsgBox "Select one contiguous range, then run the macro again."
        Exit Sub
    End If

    ' Let the user point to a cell; Cancel leaves the variable unset.
    On Error Resume Next
    Set destinationCell = Application.InputBox( _
        Prompt:="Select the top-left destination cell.", _
        Title:="Copy selected cells", Type:=8)
    On Error GoTo 0

    If destinationCell Is Nothing Then Exit Sub

    Set destinationCell = destinationCell.Cells(1, 1)
    sourceRange.Copy Destination:=destinationCell
End Sub

The macro treats the first cell of the destination selection as the starting point, even if you select a larger destination range in the prompt. Excel determines the required copy size from the source range. Make sure there is enough room to the right and below the destination; existing cells in that area can be overwritten.

How to add and run the VBA

Save a working copy of the workbook first. Macros can change cells, and their actions cannot be reversed with Ctrl+Z.
Open the Visual Basic Editor with Alt+F11 on Windows. In the editor, choose Insert > Module.
Paste the code into the module, then return to Excel. Save the file as an Excel Macro-Enabled Workbook (.xlsm) if you need the macro to remain in that workbook.
Select one contiguous range of cells and run the macro with Alt+F8. Choose CopySelectedCellsToDestination, select Run, and use the prompt to choose a destination cell.

You can also run a macro from the Developer tab. If that tab is hidden in Windows Excel, show it through File > Options > Customize Ribbon and select Developer. On Mac, VBA is available in desktop Excel, but keyboard shortcuts can depend on the keyboard and function-key settings; use the Developer tab’s Macros command or the Macros dialog if Alt+F8 or Alt+F11 is not available. Excel for the web does not run VBA macros, so open the workbook in desktop Excel to use this code.

What is copied: formulas, formatting, and more

sourceRange.Copy Destination:=destinationCell asks Excel to copy the range to the destination, rather than copying only its values. It is generally the closest VBA equivalent to copying cells and pasting them normally. For instance, copying a cell containing =A1+B1 may produce a formula with adjusted references at the destination, depending on the relative and absolute references used.

Formatting moves along with the cells, which can be helpful when duplicating a carefully formatted table. It can also be a nuisance if the destination already has its own layout. A copied number format, font, fill, border, or other cell formatting can replace the corresponding destination formatting. Consider whether the destination should inherit the source’s appearance before running a macro over a large range.

A copied formula remains a formula, not a fixed result. If the source contains a formula that refers to other cells, the copy can calculate differently when relative references shift. Dollar signs in references mark absolute row or column references; those parts do not shift in the same way. Check a few formulas at the destination when the precise references matter.

Copy values only instead

Sometimes the goal is to copy the displayed results without bringing formulas or source formatting along. In that case, use a destination range with the same number of rows and columns and assign the source values to it. The following alternative asks for a starting cell and fills a matching-size destination range with values.

Sub CopySelectedValuesToDestination()
    Dim sourceRange As Range
    Dim destinationCell As Range
    Dim destinationRange As Range

    If TypeName(Selection) <> "Range" Then Exit Sub
    Set sourceRange = Selection

    If sourceRange.Areas.Count > 1 Then
        MsgBox "Select one contiguous range."
        Exit Sub
    End If

    On Error Resume Next
    Set destinationCell = Application.InputBox( _
        Prompt:="Select the top-left destination cell.", _
        Title:="Copy values", Type:=8)
    On Error GoTo 0
    If destinationCell Is Nothing Then Exit Sub

    Set destinationCell = destinationCell.Cells(1, 1)
    Set destinationRange = destinationCell.Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Assign results, not the original formulas or formatting.
    destinationRange.Value = sourceRange.Value
End Sub

This version copies values, including the current results of formulas; it does not copy the formulas themselves or the source formatting. The destination’s existing number format remains in place, so a date or percentage may display differently if the destination is formatted as General or with an incompatible format. The assignment is suitable for a contiguous rectangular range; it is not a way to preserve separate areas or cell-by-cell formatting.

Choosing the right copy method

Use the method that matches the outcome you want, rather than assuming every paste is identical. The normal copy method is appropriate when the destination should behave like a copied block. A values-only assignment is often better for a snapshot of results or when you do not want formulas to update later.

Copy cells normally: use sourceRange.Copy Destination:=destinationCell to bring the cell contents and formatting to a new position.
Copy values only: resize a destination range to the source dimensions and assign destinationRange.Value = sourceRange.Value.
Copy manually: select the cells, press Ctrl+C, select the destination, and press Ctrl+V. On Mac, use Command+C and Command+V. This is easiest for occasional work.
Copy only a particular feature: use Excel’s Paste Special options when you need a specific combination, such as formats or formulas. A VBA macro can also use paste-special methods, but the code should be tailored to that exact requirement.

A macro is most useful when the same operation is repeated, when the destination should be selected consistently, or when it is one step in a larger workflow. For a one-time copy, the keyboard shortcuts are simpler and avoid enabling or maintaining VBA.

Common problems and how to fix them

The macro says to select cells: the active selection may be a chart, shape, or other object. Click a worksheet cell or drag across a cell range, then run the macro again.
The macro rejects the selection: a selection made with Ctrl-click can contain multiple separate areas. Select one continuous rectangle; the sample deliberately rejects separate areas to avoid unclear copy behavior.
Nothing happens after choosing the destination: Cancel exits the macro by design. Choose a worksheet cell in the prompt, then confirm that you ran the intended macro from the Macros dialog.
Existing data disappears: the destination block is overwritten by the copied cells. Check the destination and the full size of the source selection first, especially when copying a wide or tall range.
Formulas show unexpected results: relative references can change as the formula is copied. Inspect the formula bar at the destination and use absolute references where the worksheet design calls for fixed references.
Formatting looks wrong: a normal copy brings source formatting. Use the values-only version if you need to retain the destination’s existing appearance.

Because Ctrl+Z does not undo VBA actions, do not treat Undo as a recovery plan. Saving a separate copy before running unfamiliar code is the simplest protection. For repeatable production work, test the macro on a small sample or a duplicate sheet before using it on important data.

Macro security and workbook compatibility

Excel may block macros depending on the file’s source, security settings, or organizational policy. Only enable macros in a workbook from a source you trust, and do not lower Excel’s macro security settings simply to make an unknown file run. A macro can modify or delete workbook data, so inspect code before running it.

Store the code in an .xlsm workbook if the macro should travel with that workbook. A standard .xlsx file does not retain VBA code when saved in that format. If you only need the macro for your own Excel sessions, a Personal Macro Workbook is another option, but it is less convenient to share and requires care when distributing files.

VBA works in desktop Excel for Windows and Mac, although some interface details and keyboard shortcuts differ. The sample uses ordinary Excel range operations and an input box, not Windows-only automation. Excel for the web can open and edit many macro-enabled workbooks, but it cannot execute VBA; use desktop Excel for the macro itself.

Frequently Asked Questions

How do I copy the selected cells to another sheet?
Run the macro and select a destination cell on the other worksheet in the same workbook when the range prompt appears. The source selection must still be one contiguous range. Verify the destination sheet and cell before confirming, since the copy can overwrite cells there.

Does VBA copy and paste preserve formatting?
Yes, the normal Range.Copy Destination:=... approach copies formatting as well as cell contents and formulas. Use the values-only assignment shown above when you want results without the source formatting.

How can I copy selected cells without formulas?
Use the values-only macro. It assigns the source range’s values to a destination range of matching dimensions, so formulas are replaced by their current results. The destination’s number formats are not copied.

Can I copy multiple non-adjacent selections with this code?
No. The example checks Areas.Count and stops if the selection consists of separate areas. Copy each area individually or write a macro that explicitly defines how each area should map to its destination.

Why can’t I undo a VBA copy with Ctrl+Z?
Running VBA generally clears Excel’s normal undo history, and macro changes cannot be undone with Ctrl+Z. Save a backup copy before running the macro, particularly if the destination contains data that must be retained.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.