VBA Copy PasteSpecial: Values, Formats, and More

In Excel VBA, copy a range and call PasteSpecial on the destination to paste only values, formats, formulas, or another selected part of the copied cells. For example, use source.Copy followed by destination.PasteSpecial Paste:=xlPasteValues to replace formulas with their current results while leaving the destination’s formatting alone.

What VBA Copy PasteSpecial Does

Excel’s ordinary copy-and-paste operation can transfer a cell’s contents, formulas, number formats, borders, fills, comments or notes, validation rules, and other properties together. VBA’s Range.PasteSpecial lets a macro choose which parts of the copied range to paste. This is useful when automating a report, preparing a static extract, or moving data into a template with its own formatting.

Paste Special does not mean “paste what the cell looks like.” It means paste a specified category of information from the copied range. For instance, xlPasteValues transfers calculated results rather than formulas. If a cell contains =SUM(B2:B5), a values-only paste writes the result that formula currently returns as a value; it does not keep the formula in the destination.

The method is part of Excel’s desktop VBA object model. Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros. Excel for Mac supports VBA, although the editor and keyboard shortcuts can differ from Windows. The core Range.Copy and PasteSpecial statements below are standard Excel VBA.

A Basic Values-Only Macro

This example copies A2:D20 from a worksheet named Data and pastes the results into F2:I20 on that same sheet. It assumes the macro is stored in the workbook being edited. The source and destination ranges have the same dimensions.

Sub PasteValuesOnly()
    Dim ws As Worksheet
    Dim source As Range
    Dim destination As Range

    ' Use the worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set source = ws.Range("A2:D20")
    Set destination = ws.Range("F2:I20")

    ' Copy formulas as their current results, without source formatting.
    source.Copy
    destination.PasteSpecial Paste:=xlPasteValues

    ' Clear Excel's moving copy border and copy mode.
    Application.CutCopyMode = False
End Sub

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module with Insert > Module, and place the procedure there. Run it from the editor or from Excel’s Macro dialog. Before running any macro that changes a workbook, save a separate copy: VBA actions cannot be undone with Ctrl+Z. If the macro is stored in a different workbook from the data, change ThisWorkbook to an explicit workbook reference rather than relying on whichever workbook happens to be active.

Choosing What to Paste

The Paste argument takes an Excel constant that specifies the part to transfer. Common choices include:

xlPasteValues: values and formula results, without the source cell formatting.
xlPasteFormulas: formulas, without copying the source formatting.
xlPasteFormats: formatting such as number formats, fills, and borders, without the cell contents.
xlPasteAll: the usual full-copy behavior, including contents and formatting.
xlPasteValuesAndNumberFormats: values or formula results together with number formats, useful when dates, percentages, or currency should retain their display format.
xlPasteFormulasAndNumberFormats: formulas together with number formats.
xlPasteValidation: data-validation rules, such as a drop-down list rule.
xlPasteColumnWidths: the copied columns’ widths.

Use the narrowest option that meets the task. A values-only paste is often safer than a full paste when the destination is a designed report, because it does not overwrite its borders, fills, or number formats. Conversely, values alone may not be enough for a date column if the destination is formatted as General; the underlying date serial can then appear as a number. In that case, paste values and number formats, or set the destination’s number format separately.

Formats, Formulas, and Other Examples

For formulas rather than their current results, use xlPasteFormulas. If the copied formulas contain relative references, Excel adjusts them for the destination just as it does during a normal copy. For example, copying a formula that refers to the cell immediately to its left generally keeps that relative relationship when pasted elsewhere. Absolute references marked with dollar signs remain fixed according to standard Excel reference rules.

To copy formatting only, use xlPasteFormats. This can apply a source cell’s number format and visual styling without replacing destination values. It is not the same as copying column widths; use xlPasteColumnWidths when column sizing is the part you need. Column widths apply to whole worksheet columns, so consider that effect before running the macro.

PasteSpecial also accepts options for skipping blank cells and transposing rows and columns. For example, the following statement adds the copied values to corresponding destination cells and skips blank source cells:

destination.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlPasteSpecialOperationAdd, SkipBlanks:=True

The Operation argument can also use subtraction, multiplication, or division constants. These operations combine copied values with existing destination values; they are not needed for a straightforward replacement. With Transpose:=True, rows and columns switch orientation. A two-row, three-column source becomes a three-row, two-column result, so leave enough clear space in the destination area. Transposition is not appropriate for every kind of pasted content, so check the result when working with complex ranges.

Using PasteSpecial Without a Copy Operation

PasteSpecial relies on a copied range, so the usual pattern has a Copy statement before it. For a simple values-only transfer, however, direct assignment can avoid the clipboard:

Sub AssignValuesDirectly()
    Dim ws As Worksheet
    Dim source As Range
    Dim destination As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set source = ws.Range("A2:D20")
    Set destination = ws.Range("F2:I20")

    ' Transfer the current values without copying formatting.
    destination.Value = source.Value
End Sub

This approach is concise and can be efficient for same-sized ranges. It transfers the values returned by the source cells, not their formulas or formatting, and it does not use Excel’s copy mode. The source and destination should have matching dimensions for a predictable cell-for-cell result. Use Copy and PasteSpecial instead when you need formulas, formats, validation, a paste operation, or options such as transpose and skip blanks. Direct assignment is not a complete replacement for every copy-and-paste task.

Common Problems and How to Fix Them

Run-time error or no paste: confirm that the source is a valid range and that the copy statement runs immediately before PasteSpecial. Avoid clearing Application.CutCopyMode until after the paste.
Wrong worksheet or workbook: qualify ranges with a worksheet variable, as in the example. Unqualified Range("A1") refers to the active sheet, which may not be the sheet you intended.
Misaligned results: check that source and destination have matching row and column counts. When transposing, allow space for the swapped dimensions and ensure the destination area is not obstructed by existing data.
Dates or numbers look different: values-only pasting does not bring over the source number format. Use xlPasteValuesAndNumberFormats or format the destination explicitly.
Formulas became fixed numbers: that is the expected result of xlPasteValues. Choose xlPasteFormulas if the destination needs formulas that recalculate.
Some blank cells did not replace existing data: the SkipBlanks:=True option intentionally leaves corresponding destination cells unchanged. Omit it, or set it to False, when blanks should also be pasted.

Protected sheets, merged cells, filtered data, and destination ranges with incompatible layouts can also affect a paste. If a macro works on a plain test sheet but fails in a formatted report, inspect the destination for protection, merged areas, and existing content before changing the code. Do not assume that a paste error means the PasteSpecial constant itself is invalid.

Choosing Between PasteSpecial and Other Methods

Use PasteSpecial when the task is genuinely a selective paste: values but not formats, formulas with number formats, or content combined with an arithmetic operation. It closely mirrors Excel’s Paste Special interface and makes the intended paste type visible in the code.

Use direct range assignment for a straightforward transfer of values between same-sized ranges when you do not need clipboard behavior. Use a normal copy and paste, or xlPasteAll, when the destination should receive the full copied cells. These approaches are not interchangeable in every case: for example, direct assignment does not copy formatting or formulas as formulas.

Manual Excel users can copy with Ctrl+C and choose a paste option from Home > Paste in Microsoft 365 for Windows. On Mac, the common copy shortcut is Command+C; the available Paste Special shortcut differs by keyboard and Excel settings, so the ribbon menu is a reliable alternative. Those shortcuts are for the interface, not required in VBA. A macro can specify the paste type directly and does not need a user to press a keyboard shortcut.

Macro Safety and Platform Notes

Because macros change workbook contents, save a backup before running unfamiliar code. A VBA macro’s changes cannot be reversed with Ctrl+Z, even if the change seems small. Use descriptive worksheet and range references, test on a duplicate workbook, and avoid broad destinations such as an entire column unless that is truly intended.

To keep VBA code in an Excel workbook, save it in a macro-enabled format such as .xlsm. Excel for Windows may require you to enable the Developer tab through File > Options > Customize Ribbon, then select Developer. On Mac, VBA is available in the desktop application, but the editor’s entry point and keyboard shortcuts can differ. Excel for the web does not execute VBA; use desktop Excel to run the macro. Macro security settings may also block code, so only enable macros in files you trust.

Frequently Asked Questions

How do I paste values only in VBA? Copy the source range, then call destination.PasteSpecial Paste:=xlPasteValues. This writes current values and formula results, not the original formulas or source formatting.

How do I copy only formatting with VBA? Use source.Copy followed by destination.PasteSpecial Paste:=xlPasteFormats. This copies cell formatting, not the source values. Use xlPasteColumnWidths separately if you also need column widths.

Can I use PasteSpecial without selecting cells? Yes. Assign worksheet and range variables, then call Copy on the source and PasteSpecial on the destination, as in the examples. Selecting cells is generally unnecessary for this operation and can make a macro more dependent on the active sheet.

Why did PasteSpecial paste a number instead of a date? Excel stores dates as numbers and displays them using a number format. A values-only paste transfers the value but not that display format. Paste values and number formats, or apply a date format to the destination.

Can VBA PasteSpecial work in Excel for the web? No. Excel for the web can preserve a workbook containing VBA, but it cannot run VBA macros. Open the file in the Excel desktop application to execute the code.

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.