VBA Macro Copy and Paste: Methods, Examples, and Safety

In Excel VBA, copy and paste cells with the Range.Copy method: specify the source range and a destination, such as Worksheets("Data").Range("A2:D20").Copy Destination:=Worksheets("Archive").Range("A2"). This copies cell contents and formatting; for values only, assign one range’s Value to another. Save a separate copy of your workbook before running a macro, because VBA actions cannot be undone with Ctrl+Z.

What copy and paste means in VBA

VBA is Excel’s programming language, and a macro is a set of instructions that Excel can run. A copy-and-paste macro automates the same general job you might do by selecting cells, pressing Ctrl+C, and choosing a destination. The difference is that code can identify exact ranges and repeat the operation consistently without relying on the active selection.

A normal Excel copy can include formulas, displayed values, formatting, and other cell features. The exact result depends on how the macro copies the cells. For example, copying a range to a destination with Range.Copy Destination:=... is suitable when you want to carry the cells across as a whole. Assigning values directly is different: it transfers the current values, but not the source formatting or formulas.

Copying a formula is not the same as copying its displayed result. Excel usually adjusts relative references to suit the new location, just as it does when you drag or paste a formula. If you need an unchanging result rather than a live formula, use a values-only approach.

The simplest copy-and-paste macro

This example copies a block from a worksheet called Data to a worksheet called Archive. It starts at A2 on both sheets and copies the range’s formulas and cell features along with it. Change the sheet names and range addresses to match your workbook.

Sub CopyRangeToArchive()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet

    ' Refer to specific worksheets rather than the active sheet.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Archive")

    ' Copy the full cell range to the matching destination area.
    sourceSheet.Range("A2:D20").Copy _
        Destination:=targetSheet.Range("A2")
End Sub

ThisWorkbook means the workbook containing the code. Naming both worksheets makes the macro less dependent on which sheet happens to be open. The destination is the upper-left cell of the pasted block; Excel fills the matching-sized area from there. The destination does not need to be selected first.

For example, if the source is A2:D20, it has 19 rows and four columns. Starting the destination at A2 therefore fills A2:D20 on Archive. If the destination is too close to other data, the macro can overwrite it, so confirm the target area before running the code.

Copying values without formulas or formatting

To transfer only the current contents, assign the source range’s Value property to a destination range of the same dimensions. This avoids the clipboard and does not copy the source cell formatting. The following version sizes the destination automatically based on the source range.

Sub CopyValuesToArchive()
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Identify the source block.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:D20")

    ' Create a destination with the same number of rows and columns.
    Set targetRange = ThisWorkbook.Worksheets("Archive").Range("A2").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Transfer values, not formulas or formatting.
    targetRange.Value = sourceRange.Value
End Sub

If a source cell contains a formula, this method writes its calculated result into the destination rather than the formula itself. It also transfers blank cells within the selected block, so existing destination contents in those positions can be cleared. Errors in source cells are transferred as error values. The destination begins at A2 and expands to fit the 19-by-4 source range.

Paste values with PasteSpecial

Another option is to copy the range and then use Excel’s paste-special operation. This is useful when you want to use one of Excel’s specific paste types, such as values, formats, or formulas. For values only, the code can be written as follows:

Sub PasteValuesSpecial()
    Dim sourceRange As Range
    Dim targetCell As Range

    ' Set the source and the first destination cell.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:D20")
    Set targetCell = ThisWorkbook.Worksheets("Archive").Range("A2")

    ' Copy, then paste only values into the matching-sized area.
    sourceRange.Copy
    targetCell.PasteSpecial Paste:=xlPasteValues

    ' Clear the moving-copy outline and clipboard mode.
    Application.CutCopyMode = False
End Sub

PasteSpecial uses Excel’s copy-and-paste mechanism, while direct value assignment does not. For a straightforward values-only transfer, the assignment example is often shorter. Use PasteSpecial when a particular paste type is needed or when your procedure already uses Excel’s clipboard. The xlPasteValues constant is built into Excel VBA.

Copying between worksheets and workbooks

For two sheets in the same workbook, fully qualifying both worksheet references is a reliable habit. If a macro is stored in a personal macro workbook or add-in, ThisWorkbook may not be the workbook containing the data. In that situation, identify the data workbook explicitly, for example with a variable set to Workbooks("Sales.xlsx"), and then refer to its worksheets.

Copying to a different open workbook follows the same principle: identify the source workbook, source sheet, destination workbook, and destination sheet. Make sure the target workbook is open and that its name matches exactly. A macro that refers to a workbook by name will fail if that workbook is closed or has a different name. If the macro opens a file itself, it should also handle the possibility that the file cannot be found or opened.

When copying formulas between sheets or workbooks, consider whether references should continue pointing to the original sheet or workbook. Excel may adjust relative references based on the paste location, while absolute references marked with dollar signs behave differently. Test a representative formula in the destination before relying on a large automated transfer.

Choosing the right copy method

Choose the method according to what the destination should contain. The word “paste” does not always mean that every feature of the source cell should be reproduced.

Copy the range with a destination: Use Copy Destination:= when you want a normal cell copy, including formulas and formatting, without writing a separate paste step.
Assign values: Use targetRange.Value = sourceRange.Value when the destination should receive results rather than formulas, and source formatting should not be copied.
Use PasteSpecial: Use PasteSpecial when you need a specific paste type, such as values or formats, or need to control the paste step separately.
Copy formulas deliberately: A normal copy can adjust relative references. If the output must be static, paste values instead; if it must remain live, verify the resulting references.

For large blocks, direct range operations are generally preferable to copying cells one at a time. Avoid selecting each cell and using Selection unless the task truly depends on what a user has selected. Explicit range references are easier to read and are less likely to act on the wrong sheet.

Common errors and how to prevent them

A “subscript out of range” error often means a workbook or worksheet name in the code does not match an open workbook or existing sheet. Check spelling, spaces, and the workbook reference. A “method failed” or paste-related error may arise because the expected workbook or sheet is unavailable, the source and destination are incompatible, or the code relies on a selection that is not active.

Other practical issues to check include:

Unexpected overwriting: The destination range may already contain information. Inspect the target area or add code to check it before writing.
Wrong active sheet: Unqualified references such as Range("A1") can refer to the active sheet. Qualify them with a worksheet variable.
Different range sizes: Direct value assignment between differently sized ranges can produce an error or an unintended result. Resize the destination to match the source.
Merged cells: Merged areas can interfere with operations when source and destination layouts differ. Keep the merged-cell structure consistent or avoid merging cells in data ranges.
Clipboard state: Another copy operation can replace clipboard contents. Use a direct range operation or value assignment when clipboard behavior is unnecessary.

Test the macro on a small sample or a duplicate workbook first. This makes it easier to spot reference changes, lost formatting, or overwritten cells before applying the code to important data.

How to add and run a macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the code into the module, and update the example sheet names and ranges. Return to Excel and run a macro with Alt+F8, select its name, and choose Run. You can also run code from the Visual Basic Editor.

If the Developer tab is hidden in Windows Excel, enable it through File > Options > Customize Ribbon, then check Developer. On Mac, open Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar and enable Developer. Keyboard shortcuts can vary with Mac keyboard settings and function-key behavior; use the Developer tab or the Visual Basic Editor menu if a shortcut does not work.

Excel for the web does not run VBA macros. Open the workbook in the desktop Excel application to edit or run VBA. On Mac, Excel supports VBA, but some Windows-specific features, external components, and operating-system calls may not work the same way. Basic range-copy operations are generally more portable than code that depends on Windows-only tools.

Macro security and workbook format

Before running any macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even if the code performs an operation that looks like an ordinary paste. A backup is the simplest way to recover if the macro targets the wrong range or produces an unexpected result.

A workbook containing VBA generally needs to be saved in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm). Saving a macro-containing workbook as a standard .xlsx file does not preserve its VBA project. Do not enable macros in a file merely because Excel displays a prompt: run code only when you trust its source and understand why it is needed. A macro can change or delete workbook data, so review unfamiliar code before allowing it to run.

Frequently Asked Questions

How do I copy and paste a range in VBA without selecting it?
Use a fully qualified source range and the Copy Destination:= argument. For example, Worksheets("Data").Range("A1:B5").Copy Destination:=Worksheets("Archive").Range("A1") copies the block without selecting either range. Qualify worksheet references with their workbook too when needed.

How do I paste values only in a VBA macro?
Assign the source range’s Value to a same-sized destination range, or copy the source and use PasteSpecial Paste:=xlPasteValues. Direct assignment avoids the clipboard; PasteSpecial follows Excel’s copy-and-paste process.

Does VBA copy formulas or their results?
A normal range copy carries formulas, which may adjust their relative references at the destination. A values-only transfer writes the formula’s calculated result instead. Choose based on whether you want a live calculation or a fixed result.

Can I undo a VBA copy-and-paste macro?
Usually not with Ctrl+Z. Running VBA clears Excel’s ordinary undo history, so save a separate workbook copy before running a macro that changes data.

Can I run VBA macros in Excel for the web?
No. Excel for the web does not run VBA. Use the desktop Excel application for Windows or Mac to run VBA macros.

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.