Excel VBA Copy and Paste a Range to Another Workbook

To copy a range from one open Excel workbook to another with VBA, use sourceRange.Copy Destination:=targetSheet.Range("B2"), replacing the range, worksheet, and destination with your own. The destination workbook and both worksheets must be referenced explicitly so Excel knows where to copy the cells.

How copying between workbooks works

Excel’s Range.Copy method copies cells from a source range to a destination range. You can use it across worksheets and workbooks, provided the workbooks are open and your code identifies the correct worksheet in each one. The destination reference marks the top-left cell of the copied block; Excel fills the corresponding number of rows and columns.

For example, copying A1:D20 to B2 fills B2:E21. The source and destination do not need to have the same starting address, but the destination must have enough room for the copied cells. Existing destination contents in the resulting area are overwritten, so check the target before running a macro.

A normal range copy carries more than displayed text. It can include formulas, values, formatting, number formats, cell comments or notes, and other cell properties. The exact result depends on the contents and features involved; copying cells is not the same as copying a screenshot or a worksheet as a whole.

A complete VBA example

This macro assumes it is stored in the workbook containing the source data, that the destination workbook is already open, and that the named sheets exist. It copies cells A1:D20 on the Data sheet to B2 on the Archive sheet.

Sub CopyRangeToAnotherWorkbook()
    Dim sourceWb As Workbook
    Dim targetWb As Workbook
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim sourceRange As Range

    ' The workbook containing this macro is the source.
    Set sourceWb = ThisWorkbook
    Set targetWb = Workbooks("Report.xlsx")

    Set sourceWs = sourceWb.Worksheets("Data")
    Set targetWs = targetWb.Worksheets("Archive")
    Set sourceRange = sourceWs.Range("A1:D20")

    ' B2 is the top-left destination cell.
    sourceRange.Copy Destination:=targetWs.Range("B2")
End Sub

Change Report.xlsx, Data, Archive, and the range addresses to match your files. Workbook names in Workbooks(...) include the file extension and must match the open workbook’s name. If the macro is stored in a personal macro workbook or another controller workbook, ThisWorkbook refers to that file, not necessarily the workbook containing the source data. In that case, set sourceWb to the correct open workbook explicitly.

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and put the code there. On Mac, Option+F11 opens the editor on many keyboards; you may need to hold Fn as well, or use Excel’s Tools > Macro > Visual Basic Editor menu. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros.

Choose what should be copied

A cell copy is often the right choice when the destination should look and behave like the source. However, some tasks call for transferring only values or only formulas. Choose the method based on what you want the destination workbook to contain:

Everything in the cells: Use Copy Destination:= when you want the usual copied-cell result, including formulas and formatting.
Values only: Assign the source range’s Value2 to a destination range of the same dimensions. This avoids the clipboard and leaves destination formatting in place.
Formulas only: Use a formula assignment or copy and then use Paste Special for formulas. Check references carefully because formula references may change when moved.
Specific formats or attributes: Use VBA’s PasteSpecial options, such as values or formats, when you need more control than a full copy.

For a values-only transfer, use this alternative inside a procedure where the source and destination workbook and worksheet variables have already been set. The destination range is resized to match the source, so it receives the same number of rows and columns.

Dim targetRange As Range

Set targetRange = targetWs.Range("B2").Resize( _
    sourceRange.Rows.Count, sourceRange.Columns.Count)
targetRange.Value2 = sourceRange.Value2

This transfers the current cell results, not the formulas that produced them. It does not copy formatting, column widths, or other cell properties. Dates and currency values are represented internally as numbers; if the destination’s number format differs, the same value may display differently. Apply an appropriate number format if the displayed result matters.

Formulas, links, and formatting

When formulas are copied, Excel generally adjusts relative references according to the new position, as it does during a worksheet copy-and-paste operation. Absolute references marked with dollar signs behave differently from relative references. A formula that refers to another sheet or workbook may also continue to refer to its original source, or become an external reference, depending on the formula and the copy operation. Inspect important formulas in the destination rather than assuming they now refer only to the new workbook.

Formatting usually travels with a full cell copy, but the appearance can still differ if the destination workbook uses a different theme, font availability, conditional formatting rules, or column widths. A range copy does not automatically make the destination columns the same width as the source columns. If widths matter, set them separately or use the relevant Paste Special option. Likewise, copying a range does not copy worksheet-level features such as print settings, sheet protection setup, or tab color.

If you want to paste only values through the clipboard, VBA can copy the range and then use PasteSpecial with xlPasteValues. That approach is useful when you need the result of formulas but not the formulas themselves. For simple large-range transfers, direct Value2 assignment is usually more straightforward and avoids leaving Excel in copy mode.

When the destination workbook is not open

The example uses Workbooks("Report.xlsx"), which works only when that workbook is already open in the current Excel instance. If it is closed, open it first with Workbooks.Open, supplying a valid full file path. For example, the path might be C:\Reports\Report.xlsx on Windows. Paths vary by computer, and a missing file or inaccessible network location will cause an error.

When a macro opens a workbook, decide whether it should save and close that workbook afterward. Saving makes the change persistent; closing without saving discards it. Avoid automatically saving over a file unless that is intended. A cautious workflow opens the destination, copies the data, lets the user review the result, and saves only when appropriate. If the destination is already open, opening it again by path can cause confusion or an error, so check the open-workbook collection or use a predictable workflow.

Common errors and how to fix them

Subscript out of range: The workbook or worksheet name does not match an open workbook or existing sheet. Check spelling, spaces, file extensions, and whether the destination is open.
Object variable not set: A workbook, worksheet, or range variable was not assigned with Set, or the code did not find the object it expected.
Wrong source workbook: ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel, which can change unexpectedly. Prefer explicit workbook variables.
Destination data disappears: The copy overwrites cells in its destination footprint. Confirm the top-left destination cell and the dimensions of the source range before running the macro.
Values appear differently: A values-only assignment does not transfer formatting. Check the destination’s number format, especially for dates, times, percentages, and currency.
Formula results change: Relative references can shift during copying. Review formulas and decide whether the destination should retain links to the source workbook.

For code that may encounter missing sheets or files, add error handling and give the user a clear message rather than letting the macro stop with an unexplained error. Also avoid relying on whichever workbook happens to be active. Explicit references make the code easier to understand and reduce the risk of copying from or into the wrong file.

Safety and reliable macro use

Save a separate copy of the workbook before running a macro that changes data. VBA changes cannot be undone with Ctrl+Z, and a macro may overwrite a large destination area in a moment. On Windows, Ctrl+Z is Excel’s usual Undo shortcut; on Mac, Command+Z is the usual equivalent, but neither restores changes made by a VBA procedure as normal worksheet undo would.

Run macros only in files you trust. Excel’s macro security settings may block VBA or show a security warning, particularly for files downloaded from the internet. Do not enable macros simply because a workbook asks you to. If a macro is intended for routine use, test it on copies with representative data, confirm the source and destination addresses, and consider adding checks for the expected workbook names and range size.

Alternatives to VBA

If this is a one-time task, use Excel’s ordinary copy and paste: select the source cells, press Ctrl+C on Windows (Command+C on Mac), switch to the destination workbook, select its top-left cell, and paste. Use Paste Special from the Home tab when you need values or formats rather than everything. Keyboard and menu details can vary slightly by Mac version, but the copy-and-paste workflow is the same.

For repeated imports from files, Power Query may be more suitable than a macro, especially when the task is to load and reshape data rather than copy formatted cells. In Microsoft 365 for Windows, find it on the Data tab through Get Data. Excel for the web has different capabilities and does not run VBA. For an automated, repeatable cell-level transfer in desktop Excel, VBA remains useful because it can name exact workbooks, sheets, ranges, and follow-up actions.

Frequently Asked Questions

Can VBA copy a range when the other workbook is closed?
Yes, but the macro must open the destination workbook first, usually with Workbooks.Open and its full path. It can then copy the range and save or close the workbook according to the intended workflow.

Does Range.Copy copy formulas or just their displayed results?
A normal range copy copies formulas as formulas, along with other cell content and formatting. To transfer only the current results, assign the source range’s Value2 to an equally sized destination range or use Paste Special for values.

How do I copy a range without using the clipboard?
For values only, assign targetRange.Value2 = sourceRange.Value2 after making the target the same size. That transfers values without a clipboard copy. A full copy with formatting and formulas is better handled by Copy Destination:=.

Why does my macro say Subscript out of range?
Usually, the workbook name in Workbooks(...) is not open or the worksheet name in Worksheets(...) is incorrect. Verify the exact names in Excel and make sure the code is looking at the intended workbook.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the file in desktop Excel for Windows or Mac to run the macro; the available VBA experience and shortcuts differ by 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.