How to Copy and Paste Using VBA in Excel: Macro Guide

To copy a range in Excel with VBA, use Range.Copy Destination:=...; for values only, assign one range’s .Value to another. Open the Visual Basic Editor, add the appropriate macro to a module, then run it from Excel. You’ll need the desktop version of Excel and a workbook saved as a macro-enabled file. Save a backup first: VBA changes cannot be undone with Ctrl+Z.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, though some shortcuts and settings differ. Excel for the web cannot create or run VBA macros.
A workbook containing the source and destination sheets or ranges you want to use.
A macro-enabled workbook saved as .xlsm if you want to keep the macro in that file.
Permission to run macros. Only enable macros in files you trust.

Step-by-Step

Decide what you want to copy. Excel’s regular copy operation can carry cell values, formulas, formatting, comments, and other cell features. A direct values assignment copies only the displayed values; it does not bring over formatting or formulas. The examples below copy a fixed range from a sheet named Data to a sheet named Summary. Change those names and range addresses to match your workbook.
Save a backup copy. Before running any macro that changes cells, use File > Save As to create a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the dependable way to recover the original data if the result is wrong.
Save the working file as macro-enabled. In Excel for Windows, choose File > Save As, select a location, and choose Excel Macro-Enabled Workbook (*.xlsm) in the file type list. If you save a macro in a standard .xlsx file, Excel will warn that the VBA project cannot be retained. Choose the macro-enabled format to keep your code.
Open the VBA editor and insert a module. On Windows, press Alt+F11, or select Developer > Visual Basic if the Developer tab is visible. In the editor, select Insert > Module. On a Mac, open Tools > Macro > Visual Basic Editor, or use the Developer tab if available; the keyboard shortcut is usually Option+F11 and may require the Fn key depending on your keyboard. In the editor, select Insert > Module.

If Developer is not shown in Windows Excel, enable it through File > Options > Customize Ribbon, check Developer in the main tabs list, and select OK. On Mac, use Excel > Settings (or Preferences) > Ribbon & Toolbar and enable Developer.
Paste a basic copy macro into the module. This version copies the source cells to the destination and brings along their formulas, formatting, and other copied cell content. It does not require selecting or activating either sheet.
```
Sub CopyRangeToAnotherSheet()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Set the source and destination worksheets.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Summary")

    ' Copy the range to the destination's top-left cell.
    sourceSheet.Range("A1:C10").Copy _
        Destination:=destinationSheet.Range("A1")
End Sub
```
ThisWorkbook means the workbook containing the macro. The source range is A1:C10, and the copied block starts at A1 on Summary. If your workbook uses different sheet names, replace Data and Summary exactly, including spaces and punctuation. The destination block will occupy the same number of rows and columns as the source.
Choose a values-only approach if needed. If the destination should receive results but not formulas or formatting, assign the values directly. The ranges must have matching dimensions; this example uses ten rows and three columns in both places.
```
Sub CopyValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Refer to the worksheets in this workbook.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Summary")

    ' Transfer values without formulas or formatting.
    destinationSheet.Range("A1:C10").Value = _
        sourceSheet.Range("A1:C10").Value
End Sub
```
This method does not use the Windows clipboard and leaves the destination’s existing formatting in place. If the source cells contain formulas, the destination gets their current calculated results, not the formulas themselves.
Use PasteSpecial when you need a particular part of the copy. For example, this macro copies just values using Excel’s paste operation. Copying first and then pasting values can be useful when you want a PasteSpecial option, but it uses the clipboard.
```
Sub PasteValuesOnly()
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Set the range to copy and the paste starting cell.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:C10")
    Set destinationCell = ThisWorkbook.Worksheets("Summary").Range("A1")

    ' Paste values, then clear copy mode.
    sourceRange.Copy
    destinationCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub
```
To paste formats instead, use xlPasteFormats in the PasteSpecial line. To paste formulas, use xlPasteFormulas. These options paste at the destination cell and fill an area the size of the copied range.
Run the macro. Return to Excel, select Developer > Macros, choose the macro name, and select Run. Alternatively, in the VBA editor, click inside the procedure and press F5. On Mac, select the macro through Tools > Macro > Macros and run it. If Excel displays a security warning, only enable macros when you trust the workbook and its source.
Check the result before using it further. Confirm the intended destination cells changed, formulas or values are what you expected, and the paste did not overwrite important information. If the output is incorrect, close without saving and reopen the backup, or restore the saved copy; Ctrl+Z will not roll back a macro.

Common Mistakes

Using a sheet name that does not match. VBA requires the worksheet tab name exactly as shown in Excel. A trailing space or renamed tab can cause a “Subscript out of range” error.
Copying to the wrong workbook. ThisWorkbook refers to the file containing the macro, which may not be the active workbook. If your source data is in another workbook, explicitly reference that workbook instead of assuming the active file is the right one.
Expecting a values-only copy to preserve formulas or formatting. The .Value assignment transfers values only. Use .Copy Destination:=... for a regular copy, or a suitable PasteSpecial option for a specific type of content.
Using ranges of different sizes for direct assignment. A direct range-to-range assignment should use source and destination ranges with matching row and column counts. For a copied block that expands from one cell, use the Copy method or define a destination range of the same size.
Saving in the wrong format or disabling macros. A macro saved in an .xlsx file will not be preserved as VBA. A security setting can also prevent a macro from running; do not bypass it for a file you do not trust.

Tips

Use explicit worksheet references, as in the examples, instead of relying on whichever sheet happens to be active. This makes a macro more predictable.
Prefer direct value assignment when you only need values. It avoids selecting cells, is concise, and does not leave a copy selection active.
Use Copy Destination:= when you want a straightforward copy without separately calling Paste. Use PasteSpecial when you need to control whether values, formulas, or formats are pasted.
Keep a small test range while developing the macro. Check the output first, then update the range address for the full dataset.
For a one-off task, you can run a macro from Developer > Macros without adding a button. If you do add a button, label it clearly and confirm its assigned macro before sharing the workbook.

Troubleshooting

“Subscript out of range” appears: Check that the worksheet name in the code matches the tab name exactly and that the referenced sheet is in the workbook named by ThisWorkbook. If the sheet belongs to another open workbook, update the workbook reference.
The macro is missing from the Macros list: Make sure the code is in a standard module, the procedure begins with Sub and ends with End Sub, and it has no required arguments. Confirm the file was saved as .xlsm. Excel for the web does not run VBA, so open the workbook in desktop Excel.
Excel says macros are disabled: Close the file and check that it came from a source you trust. On Windows, review File > Options > Trust Center > Trust Center Settings > Macro Settings, following your organization’s policy. Do not enable all macros globally just to run an unknown file. On Mac, check Excel’s macro security settings in Excel > Settings or Preferences.
The destination has unexpected formulas or formats: Confirm which method the code uses. A normal Copy carries more than values, while direct .Value assignment excludes formulas and formatting. Choose the method that matches your intended result, restore the backup if needed, and run the corrected macro.
PasteSpecial fails or the clipboard appears stuck: Make sure the source range is copied immediately before PasteSpecial and that the destination sheet is not protected against editing. If the macro stops after copying, run Application.CutCopyMode = False after fixing the error to clear Excel’s copy mode.

Frequently Asked Questions

Can VBA copy and paste between worksheets?
Yes. Reference the source and destination worksheets explicitly, then use Copy Destination:= or a PasteSpecial method. Both sheets can be in the same workbook.

How do I copy only values, not formulas?
Assign the source range’s .Value to an equally sized destination range, or copy the source and use PasteSpecial Paste:=xlPasteValues. The first option also avoids copying formats.

Can I copy between two workbooks?
Yes. Both workbooks must be open if you reference them directly. Identify each workbook and worksheet explicitly, and take care that the macro does not accidentally use a different active workbook.

Can I undo a VBA copy with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a separate backup before running code that changes a workbook.

Does this work in Excel for the web?
No. Excel for the web can preserve a VBA project in a workbook, but it cannot create, edit, or run VBA macros. Open the file in desktop Excel to use these examples.

Do the same steps work on Mac?
The VBA code is generally the same in desktop Excel for Mac. The editor and macro menus differ, and the Windows Alt+F11 shortcut is usually Option+F11 on Mac, sometimes with Fn. Excel for the web does not support running VBA on either platform.

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.