To copy a range in Excel VBA, use Range.Copy Destination:= followed by the top-left destination cell; Excel copies the cells’ contents and formatting. For example, a macro can copy A1:D10 on a sheet named Source to A1 on a sheet named Archive. Save a backup first: VBA macros cannot be undone with Ctrl+Z.
A basic VBA copy-and-paste macro
This example copies a fixed block between two worksheets in the workbook that contains the macro. Change the sheet names and range to match your file.
Sub CopyBlock()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
' Refer to sheets in the workbook containing this code.
Set sourceSheet = ThisWorkbook.Worksheets("Source")
Set destinationSheet = ThisWorkbook.Worksheets("Archive")
' Copy the block, including cell contents and formatting.
sourceSheet.Range("A1:D10").Copy _
Destination:=destinationSheet.Range("A1")
End Sub
The source and destination blocks do not have to be written with matching addresses. The destination argument identifies the top-left cell where the copied block should begin, and Excel fills the corresponding number of rows and columns. If either worksheet name is missing or misspelled, the macro stops with a runtime error. Confirm the tab names before running it.
ThisWorkbook means the workbook that contains the VBA code. That is usually the right choice when the macro is stored in the workbook being edited. It is different from ActiveWorkbook, which means whichever workbook window is currently active. If code is stored in the Personal Macro Workbook or an add-in, ThisWorkbook may refer to that file rather than the data workbook; in that situation, explicitly identify the target workbook instead of assuming which one is active.
What a VBA copy actually transfers
Using Copy Destination is similar to copying and pasting cells through Excel’s interface. It can transfer values, formulas, cell formatting, and other cell-level properties. When formulas are copied in the usual way, Excel adjusts relative references to suit the destination, just as it does with a normal worksheet copy. Absolute references marked with dollar signs remain fixed according to standard Excel behavior.
A range copy is not always the same as duplicating an entire worksheet. It does not automatically reproduce every sheet-level feature, such as the worksheet’s name, tab color, print setup, or all column-width settings. Copying a range also writes over cells at the destination. Check whether the target area contains information you need to keep. If matching column widths matter, copy or set those separately rather than assuming a cell-range copy will reproduce the whole layout.
When only a rectangular block is needed, specify that block rather than copying entire rows or columns. Whole-column copies can involve more than a million rows per column in modern Excel, so they may take longer or affect more cells than intended.
Copy values without formulas or formatting
For a report or archive that should contain the current results but not the source formulas, assign the source range’s Value to a destination range of the same dimensions. This method does not use the clipboard and leaves the destination’s existing formatting in place.
Sub CopyValuesOnly()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Dim destinationRange As Range
' Set the worksheets and matching-sized ranges.
Set sourceSheet = ThisWorkbook.Worksheets("Source")
Set destinationSheet = ThisWorkbook.Worksheets("Archive")
Set sourceRange = sourceSheet.Range("A1:D10")
Set destinationRange = destinationSheet.Range("A1").Resize( _
sourceRange.Rows.Count, sourceRange.Columns.Count)
' Transfer the calculated cell values, not formulas or formats.
destinationRange.Value = sourceRange.Value
End Sub
The matching dimensions matter: the destination is resized here to have the same number of rows and columns as the source. This approach transfers the values Excel currently holds, including text, numbers, dates as stored values, and error values. It does not copy number formats, borders, comments, or formulas. A date value, for instance, may appear as a serial number if the destination cell has General formatting; apply an appropriate number format if the displayed date matters.
For a one-time paste that needs Excel’s paste options, VBA can also use PasteSpecial, such as xlPasteValues. Direct value assignment is often simpler for a values-only transfer, while copying and using a paste option is useful when you need a specific paste behavior supported by Excel.
Copy a changing number of rows
When new records are added regularly, a fixed range such as A2:D10 will eventually omit later rows. The following example finds the last nonempty cell in column A and copies columns A through D from row 2 to that row. It assumes row 1 contains headings and column A is populated for every record.
Sub CopyCurrentRecords()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim lastRow As Long
Dim sourceRange As Range
' Identify the source and destination worksheets.
Set sourceSheet = ThisWorkbook.Worksheets("Source")
Set destinationSheet = ThisWorkbook.Worksheets("Archive")
' Find the last used row in column A, starting from the bottom.
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
' Stop if there are no records beneath the header row.
If lastRow < 2 Then Exit Sub
' Copy all record rows across columns A through D.
Set sourceRange = sourceSheet.Range("A2:D" & lastRow)
sourceRange.Copy Destination:=destinationSheet.Range("A2")
End Sub
The row-finding technique depends on the chosen column. If column A has blanks inside the data or some records lack an entry there, it may report a row earlier than the actual end of the table. Choose a column that is reliably filled, or use an Excel Table and refer to its data range for a more structured workflow. Also decide whether each run should append records or replace the previous output. This example starts at Archive!A2 every time, so it overwrites the corresponding destination cells rather than adding below existing records.
Choosing the right copy method
Pick the method based on what the destination should contain, not just on what is easiest to type. A useful rule of thumb is:
- Use
Copy Destinationwhen you want a normal Excel-style cell copy, including formulas and formatting. - Assign
Valuewhen you want results only and want to leave destination formatting untouched. - Use a specific paste option when you need a feature such as values, formats, or column widths without transferring everything.
- Use a table-aware approach when the data grows, has headers, or needs to be appended without overwriting earlier output.
A common misconception is that “paste values” means the destination will look exactly like the source. It means the values are transferred, not the number formats. Another is that copying a formula always preserves its original references. A normal copy adjusts relative references; if the destination must retain a particular formula reference, review the formula’s relative, absolute, or mixed references before copying.
Put the macro in Excel and run it
In Excel for Microsoft 365 on Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module. Paste the code into the module, return to Excel, and run it from the Macro dialog or the Visual Basic Editor. The Developer tab can be enabled through File > Options > Customize Ribbon if it is not visible. You can then use Developer > Macros to select and run a macro.
On Excel for Mac, VBA is available in the desktop application, but menus and keyboard shortcuts can differ by keyboard and version. The Visual Basic Editor can be opened from Tools > Macro > Visual Basic Editor; the keyboard shortcut may require Option+F11 or Fn+Option+F11. Excel for the web does not create or run VBA macros, so open the workbook in desktop Excel to use this code.
If the workbook must retain its macro, save it as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. Saving as a standard .xlsx file does not preserve VBA code. If Excel displays a security warning, run macros only in files from a source you trust. Do not lower macro security settings broadly just to run an unfamiliar file.
Safety and troubleshooting
Before running a macro that changes cells, save a separate copy of the workbook or test on sample data. VBA changes cannot be reversed with Ctrl+Z, and a macro may overwrite a large area instantly. Check the source and destination sheet names, the range addresses, and whether the target already contains data. If the macro is intended to replace old output, consider clearing only the intended output range and making that behavior explicit.
If a macro reports “Subscript out of range,” a worksheet or workbook reference is likely incorrect. If it reports “Method ‘Range’ of object ‘_Worksheet’ failed,” inspect the range address and confirm the referenced sheet is valid. If the copied result is blank or incomplete, verify that the chosen last-row column is populated consistently and that the source range includes the intended rows. If formulas show unexpected results after copying, inspect relative references and the destination location.
Macros are also affected by workbook context. Code using ActiveSheet or Selection can act on a different sheet or range if the user changes the active window. Explicit references such as ThisWorkbook.Worksheets("Source") are usually easier to audit. For repeated business processes, consider validating the expected workbook and sheets at the start of the macro before it changes anything.
Frequently Asked Questions
How do I copy and paste a range in Excel VBA?
Use sourceRange.Copy Destination:=destinationCell, with the source range and destination cell explicitly qualified by their worksheets. This performs a normal cell copy, including formulas and formatting.
How do I paste values only in VBA?
Assign the source range’s Value to a destination range of equal size. This transfers values without formulas or formatting. If you need Excel’s paste options, copy the range and use PasteSpecial Paste:=xlPasteValues.
Can VBA copy cells between worksheets?
Yes. Set worksheet variables for the source and destination, then copy the source range to a cell on the destination sheet. Make sure both worksheet names exist in the workbook you reference.
Why can’t I undo a VBA paste?
Running a macro clears Excel’s normal Undo history, so Ctrl+Z cannot reverse its changes. Save a backup before running a macro, especially when it overwrites or clears data.
Will this VBA macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac, and save the file in a macro-enabled format if you need to keep the VBA code.
Related
- Excel Number Format to Millions: Steps and Examples
- Excel Highlight Active Cell Row and Column Automatically
- Excel: How to Calculate Workdays Between Two Dates
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro If Statement: VBA Syntax and Examples GuideAn Excel macro uses VBA’s If...Then...Else statement to make a decision and run different instructions depending on whether…
- Excel VBA: Copy and Paste a Dynamic Range AutomaticallyTo copy a range that grows or shrinks, use VBA to find its last populated row and column,…
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
- Excel VBA Code to Send an Email With an AttachmentTo send an email with an attachment from Excel on Windows, use VBA to create a message through…
- Access VBA Date Conversion: Parse, Format, and ValidateIn Access VBA, use CDate to convert a value VBA already recognizes as a date, DateSerial to build…
- VBA InputBox Format: Syntax, Types, and Examples GuideIn VBA, the basic InputBox format is InputBox(prompt, title, default): it displays a dialog and returns the user’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.