Excel VBA PasteSpecial: Values, Formats, and More

In Excel VBA, use Range.PasteSpecial after copying a source range to paste only selected parts, such as values, formulas, or formats. For example, copy a range and call PasteSpecial Paste:=xlPasteValues on the destination’s top-left cell to paste results without the original formulas.

What PasteSpecial Does in VBA

Excel’s Paste Special feature lets you choose what to carry from copied cells into the destination. The VBA method, Range.PasteSpecial, exposes the same idea in code: you can paste values without formulas, formats without data, or other supported parts of a copied range. This is useful when automating a report, preparing data for export, or combining worksheets where the source formatting or formulas should not come along.

It is different from assigning one range directly to another. A normal copy-and-paste can transfer a broad set of cell attributes; PasteSpecial narrows the transfer to the category you specify. Also, it is not a worksheet formula: it is a VBA method that runs as part of a macro. Excel for the web does not run VBA macros, so use desktop Excel for this approach.

Basic Syntax and the Copy-Then-Paste Pattern

The usual pattern is to copy a source range, then invoke PasteSpecial on the destination range. The destination can be a single cell at the upper-left corner of the target area; Excel fills the corresponding rows and columns from the copied range. Qualify both ranges with worksheet variables or worksheet names so the macro does not depend on whichever sheet happens to be active.

This example copies cells A2:D20 from a worksheet named Data and pastes the calculated values into the same address on a worksheet named Archive. Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Sub PasteValuesOnly()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Refer to sheets in the workbook that contains this macro.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")

    ' Copy the source, then paste only its displayed cell results.
    wsSource.Range("A2:D20").Copy
    wsTarget.Range("A2").PasteSpecial Paste:=xlPasteValues

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

The sample assumes both named worksheets exist and that the destination cells are suitable for the pasted data. If the destination already contains information, the pasted values replace the relevant cells. Change the sheet names and ranges to match your workbook.

Choosing the Paste Type

The Paste argument identifies which parts of the copied cells Excel should transfer. Common choices include:

xlPasteValues pastes results rather than formulas. A formula such as =SUM(B2:B5) becomes its current calculated result.
xlPasteFormulas pastes formulas without copying the source cell formatting. Relative references can adjust to the destination position, as they do in a regular paste.
xlPasteFormats transfers formatting such as number formats, fonts, fills, borders, and alignment, but not the cell values.
xlPasteFormulasAndNumberFormats transfers formulas and number formats together.
xlPasteValuesAndNumberFormats transfers results and their number formats, which can help retain dates, percentages, and currency display.
xlPasteColumnWidths transfers column widths rather than cell contents.
xlPasteValidation transfers data-validation settings, such as a list dropdown rule.

Other supported constants cover options such as all content, comments or notes, and formats with some exceptions. For a dependable macro, use the specific constant that describes the result you want rather than relying on a generic paste. In particular, “values” and “values plus number formats” are not identical: the former may leave the destination’s existing formatting in place.

Operation, SkipBlanks, and Transpose

PasteSpecial has optional arguments beyond the paste type. Operation can combine copied numbers with existing destination numbers using addition, subtraction, multiplication, or division. For example, selecting xlPasteSpecialOperationAdd adds each copied numeric value to the corresponding destination value rather than simply replacing it. The operation applies cell by cell, so the source and destination areas need to line up as intended. Use xlPasteSpecialOperationNone when you do not want an arithmetic operation.

SkipBlanks controls what happens when the copied source contains blank cells. With it set to True, blank source cells are skipped and existing destination contents in those positions are left alone. With it set to False, the blanks are included in the paste and can clear destination contents. This is useful when an update range has gaps, but it is also a frequent source of unexpected results.

Transpose changes rows into columns and columns into rows during the paste. A source range with three rows and two columns becomes a destination arrangement with two rows and three columns. Make sure the resulting destination area is clear and does not overlap data you need to retain.

Examples for Common Tasks

To paste formats only, copy the source as usual and specify xlPasteFormats. This is useful for applying a consistent heading style to another table without replacing its labels or numbers. To paste results while retaining currency or date display, choose xlPasteValuesAndNumberFormats. If the destination already has the desired formatting, plain xlPasteValues is usually the narrower choice.

For a transpose, the same basic copy-and-paste pattern applies, with Transpose:=True added to the call. For arithmetic, set the operation argument explicitly. These named arguments make the code easier to review than relying on the order of optional parameters:

destinationRange.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlPasteSpecialOperationNone, _
    SkipBlanks:=True, _
    Transpose:=False

Here, destinationRange must already refer to a valid Excel range, and a source range must have been copied before this call. This fragment is not a complete macro by itself; include declared worksheet and range variables in a full procedure. Use SkipBlanks:=True only when you deliberately want blank source cells to leave destination cells unchanged.

Common Errors and How to Fix Them

“PasteSpecial method of Range class failed.” Confirm that a range has been copied immediately beforehand and that Excel is in a state where the paste is allowed. Check that the source and destination are on usable sheets and that the destination is not blocked by a protected-sheet restriction.
The macro pastes to the wrong sheet. Unqualified references such as Range("A1") act on the active sheet. Use worksheet-qualified references such as wsTarget.Range("A1") to make the destination explicit.
Existing values disappear. A paste can replace destination cells. Check the source and destination addresses, and use SkipBlanks:=True if blank source cells should not clear existing destination cells.
Numbers look different after the paste. Values can be correct while the destination’s number format displays them differently. Use xlPasteValuesAndNumberFormats when that is the intended result, or apply an appropriate format separately.
The code relies on selection. Avoid Select and Activate when possible. Directly qualifying the ranges makes a macro less sensitive to the user’s current selection and reduces accidental pastes.

After a successful paste, Application.CutCopyMode = False exits copy mode and removes Excel’s moving border. It is cleanup, not a substitute for checking that the correct range was copied and pasted. When debugging, verify the sheet names, range sizes, and workbook references before changing the paste type.

When Direct Assignment Is Better

If the goal is to transfer formula results as values between two same-sized ranges, direct assignment can be simpler and avoids the clipboard. For example, after declaring src and dst as Range variables and setting them to equally sized areas, dst.Value = src.Value transfers the values. It does not copy formatting, column widths, or validation settings.

Direct assignment is often a good fit for data-only processing inside a macro. PasteSpecial is the better fit when you need its specific paste options, such as formats, number formats, transpose, skip blanks, or arithmetic operations. The choice is about the desired behavior, not a rule that one method is always faster or safer. For large jobs, avoid repeatedly copying one cell at a time; work with suitably sized ranges.

Excel Versions, Mac, and Macro Safety

Excel desktop for Windows and Mac supports VBA and the Range PasteSpecial method, though ribbon layouts and keyboard shortcuts can differ. In Windows Excel, Alt+F11 opens the Visual Basic Editor; on many Mac keyboards, Option+F11 opens it, sometimes with the Fn key as well. The Developer tab can also provide access to Visual Basic when enabled. Excel for the web can open a macro-enabled workbook but cannot run or edit its VBA macros.

Keep macros from sources you trust, and follow your organization’s macro-security rules rather than enabling unknown code. If the workbook itself must retain VBA, save it in a macro-enabled format such as .xlsm; saving as .xlsx does not preserve the VBA project. Keep a separate backup before running a macro that changes or replaces worksheet contents, since Ctrl+Z does not undo VBA actions.

Frequently Asked Questions

How do I paste values only in Excel VBA?

Copy the source range, then call PasteSpecial Paste:=xlPasteValues on the destination cell or range. The destination receives the current results, not the source formulas.

Can I use PasteSpecial without selecting cells?

Yes. Copy a fully qualified source range and call PasteSpecial on a fully qualified destination range. Avoiding selection makes the macro less dependent on the active sheet.

How do I paste formats only?

Copy the cells whose appearance you want, then use PasteSpecial Paste:=xlPasteFormats. This transfers formatting without replacing the destination’s cell values.

What does SkipBlanks do in VBA PasteSpecial?

When SkipBlanks:=True, blank cells in the copied range do not overwrite corresponding destination cells. When it is False, blanks are part of the paste and may clear destination contents.

Why does PasteSpecial fail in Excel for the web?

Excel for the web does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac to run a VBA procedure that uses PasteSpecial.

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.