VBA Copy and Paste Special: Examples and Options

In Excel VBA, copy a range and paste only selected content with Range.Copy followed by Range.PasteSpecial. For example, use Destination.PasteSpecial Paste:=xlPasteValues to paste calculated results without formulas; use other xlPaste options to transfer formulas, formats, comments, validation, or other parts.

How VBA Paste Special works

Excel’s Paste Special command separates what is copied from how it is pasted. In VBA, the source range is copied first, then the destination range’s PasteSpecial method specifies which parts to place there. This is useful when a worksheet needs the results of formulas but not the formulas themselves, or when you want a consistent format without replacing existing values.

The basic pattern is:

Sub PasteValuesExample()
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Set the ranges on the active worksheet.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A10")
    Set destinationRange = ThisWorkbook.Worksheets("Sheet1").Range("C2")

    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

The destination starts at C2, and Excel fills cells below it to match the source range’s dimensions. The source cells in this example are A2:A10. Replace the worksheet name and addresses with those in your workbook. If the source contains formulas, the destination receives their current results rather than the formulas.

Choosing what to paste

The Paste argument takes an Excel XlPasteType constant. Choosing the specific type is clearer and safer than relying on the default, which pastes everything. Common choices include:

xlPasteAll copies all content and formatting, much like a normal paste.
xlPasteValues pastes values, including formula results, but not formulas or cell formatting.
xlPasteFormulas pastes formulas without the source’s formatting.
xlPasteFormats pastes formatting without replacing the destination’s values.
xlPasteValidation copies data-validation rules, such as a dropdown list.
xlPasteColumnWidths copies column widths.
xlPasteValuesAndNumberFormats pastes results along with number formats, which is useful for dates, percentages, and currency.

A value-only paste does not mean “copy the text as it looks on screen.” Excel transfers the underlying value. A date, for example, is stored as a serial number; without a suitable number format, the destination may show a number instead of a date. Use xlPasteValuesAndNumberFormats when the displayed numeric format matters, or apply a format to the destination separately.

Practical VBA examples

To transfer formula results while keeping their number formats, use this macro. It copies A2:A10 to C2:C10 on the named worksheet; the destination’s other formatting is not copied.

Sub PasteValuesAndNumberFormats()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("A2:A10")
    Set destinationRange = ws.Range("C2")

    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

To paste formatting alone, change the paste type. The values and formulas already in C2:C10 remain in place, while the source formatting is applied.

Sub PasteFormatsOnly()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("A2:A10")
    Set destinationRange = ws.Range("C2")

    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

Paste Special can also apply an operation to the destination. For instance, the following adds each copied value to the corresponding destination cell. The ranges should have matching dimensions, and this operation is appropriate only when adding is genuinely intended.

Sub AddCopiedValues()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("A2:A10")
    Set destinationRange = ws.Range("C2:C10")

    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlPasteSpecialOperationAdd
    Application.CutCopyMode = False
End Sub

PasteSpecial arguments: operation, blanks, and transpose

In addition to Paste, the method accepts Operation, SkipBlanks, and Transpose. Operation can apply addition, subtraction, multiplication, or division to the destination cells. Use xlNone when no arithmetic operation is required; this is the ordinary behavior. An operation changes destination data, so confirm the ranges and intended calculation before running it.

Setting SkipBlanks:=True tells Excel not to overwrite destination cells with blank cells from the copied range. This can help when the source contains gaps but you want existing destination entries preserved. It does not mean that cells containing formulas returning an empty string are necessarily the same as truly blank cells; those formulas still contain formulas.

Setting Transpose:=True switches rows and columns during the paste. A horizontal source can therefore be pasted vertically, or a vertical source horizontally. Check the resulting footprint before running a macro, because the output dimensions are reversed. The method’s general form is destinationRange.PasteSpecial Paste:=..., Operation:=..., SkipBlanks:=..., Transpose:=...; omit optional arguments when their defaults suit the task.

Using Copy Destination instead

If you want a normal copy of everything and do not need Paste Special options, VBA offers a shorter method: sourceRange.Copy Destination:=destinationRange. It avoids the separate paste step and is often convenient for copying a complete block, including formulas and formatting. It is not a substitute for value-only or format-only pasting, since it does not let you choose those Paste Special modes.

For example, with a source range in A2:B10 and a destination beginning at D2 on the same worksheet, Range("A2:B10").Copy Destination:=Range("D2") copies the full block. In production code, qualify both ranges with a worksheet object rather than relying on whichever sheet happens to be active. When formulas are copied, Excel adjusts relative references in the usual way; absolute references marked with dollar signs remain fixed.

Common problems and how to avoid them

Wrong worksheet or workbook: Unqualified references such as Range("A1") refer to the active sheet. Set a worksheet variable and use ws.Range(...) so the macro acts on the intended sheet.
Unexpected output size: A single-cell destination is commonly used as the top-left starting cell, and the copied block fills adjacent cells. Make sure those cells are clear or that overwriting them is intentional.
Dates or percentages look wrong: Value-only paste does not bring over number formats. Choose xlPasteValuesAndNumberFormats when appropriate, or format the destination explicitly.
Formulas changed after copying: Relative references adjust when formulas are moved or copied. If the destination must contain fixed results, paste values; otherwise review whether references should be relative, absolute, or mixed.
Paste Special fails or behaves unexpectedly: Confirm the source is a valid range, the destination is writable, and the workbook is not protected in a way that blocks the edit. Merged cells, filtered ranges, and incompatible range shapes can also complicate pasting.
Blank source cells erase useful data: Use SkipBlanks:=True if blank source cells should leave corresponding destination cells unchanged.

When copying between worksheets, explicitly set both ranges to their respective sheets. Copying between workbooks is also possible, but qualify the source and destination workbook objects and make sure the destination workbook is open and editable. Avoid selecting or activating sheets simply to make a macro work: direct object references are less dependent on the user’s current screen state.

Clipboard behavior and alternatives

Range.Copy followed by PasteSpecial uses Excel’s copy-and-paste state. After pasting, Application.CutCopyMode = False clears the moving copy border and exits copy mode. It does not undo the paste. If the macro ends early because of an error, copy mode may remain active, so error handling can be useful in larger procedures.

For some tasks, assigning values directly avoids the clipboard. For example, when source and destination ranges have the same dimensions, destinationRange.Value = sourceRange.Value transfers values without a copy operation. This is not identical to every Paste Special mode: it does not copy formats, and it is best used with ranges of matching size. Paste Special remains the more flexible choice when you need Excel’s specific paste types, arithmetic operations, blank-skipping, or transposition.

Running macros safely

VBA macros can change many cells quickly, and changes made by a macro generally cannot be reversed with Ctrl+Z. Save a separate copy of the workbook before running unfamiliar or destructive code. Review the source and destination addresses, especially when a macro fills a large area or applies an arithmetic operation.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and put the procedure there. Run it from the editor or from Excel’s Macro dialog. On Mac, Excel also supports VBA, but keyboard shortcuts can depend on the keyboard and function-key settings; use the Developer tab or the Mac Visual Basic Editor menus if a shortcut differs. Excel for the web can open workbooks containing macros, but it does not run VBA macros, so use the desktop Excel application.

Frequently Asked Questions

How do I paste values only in VBA?
Copy the source range, then call destinationRange.PasteSpecial Paste:=xlPasteValues. This writes values, including formula results, without copying formulas or source formatting.

How do I paste formats only in VBA?
Use destinationRange.PasteSpecial Paste:=xlPasteFormats after copying the source. Destination values and formulas are not replaced by the source’s values.

Can VBA PasteSpecial copy column widths?
Yes. Copy a source range that includes the relevant columns and use Paste:=xlPasteColumnWidths. Check the destination columns first, because their widths will be changed.

Why does my pasted date appear as a number?
A value-only paste transfers the date’s stored serial value but not necessarily its date format. Use xlPasteValuesAndNumberFormats or apply a date format to the destination cells.

Can I paste without using the clipboard?
For values in equal-sized ranges, assigning the source range’s Value property to the destination’s Value property is an alternative. It does not offer Paste Special’s formatting and operation options.

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

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.