Excel Paste Special: What It Does & Macro Use

Excel’s Paste Special lets you choose exactly what gets pasted—values, formats, formulas, or column widths—instead of everything at once. A macro can automate Paste Special so you can apply the same paste operation to many ranges with a single shortcut or button. This article explains what Paste Special does, how to record or write a macro for it, and when each approach makes sense.

What Paste Special Actually Does

When you copy a cell and press Ctrl+V, Excel pastes everything: the value, number format, font, borders, fill color, column width, and any formula. Paste Special breaks that bundle apart. The Paste Special dialog (Ctrl+Alt+V on Windows, Cmd+Ctrl+V on Mac) gives you a grid of options:

• All – same as normal paste, but also copies column widths if you choose that option.
• Formulas – pastes the formula text, not the calculated result.
• Values – pastes only the displayed numbers or text, dropping formulas and formats.
• Formats – pastes only number format, font, borders, and fill.
• Comments – pastes only cell notes.
• Validation – pastes data validation rules.
• All using source theme – pastes everything but converts to the destination workbook’s theme.
• All except borders – keeps everything but border lines.
• Column widths – pastes only the width of the copied columns.
• Formulas and number formats – pastes formulas and their number formatting, but not fonts or fills.
• Values and number formats – pastes the values and how they look (percent, currency, etc.), but no formulas.

Below that are Operation buttons (Add, Subtract, Multiply, Divide) that apply a math operation to the destination cells, and checkboxes for Skip blanks and Transpose. The Operation buttons are the least understood: if you copy a cell containing 10, select a range, and choose Paste Special > Multiply, every selected cell gets multiplied by 10.

Why Use a Macro for Paste Special?

Paste Special is already quick with a keyboard shortcut, but a macro helps when you do the same paste operation repeatedly. For example, you might copy a total from a formula-heavy sheet and paste only values into a report every day. Recording that once as a macro lets you replay it with one click, and you can assign it to a custom ribbon button or a keyboard shortcut.

Macros also let you combine Paste Special with other steps: copy the source range, switch to the destination sheet, paste values, then clear the clipboard. A recorded macro captures every step, so you don’t have to remember the sequence.

How to Record a Paste Special Macro

Recording is the easiest way to create a macro without writing code. The steps are the same in Excel 2016 through Microsoft 365:

1. Go to the View tab (or Developer tab if you have it enabled) and click Macros > Record Macro.
2. Give the macro a name (no spaces, e.g., PasteValuesOnly).
3. Choose where to store it: This Workbook keeps it in the current file; Personal Macro Workbook makes it available in all your workbooks.
4. Perform your copy and Paste Special steps exactly as you want them recorded. For instance, select a range, press Ctrl+C, click the destination cell, then press Ctrl+Alt+V, choose Values, and press Enter.
5. Click Stop Recording.

To run it later, press Alt+F8, select the macro, and click Run. You can also assign the macro to a button on the Quick Access Toolbar or a keyboard shortcut in the Macro dialog.

Writing a Simple VBA Macro for Paste Special

If you need more control, you can write the VBA code directly. Open the Visual Basic Editor with Alt+F11, insert a new module, and type a procedure like this:

Sub PasteValuesOnly()
    Selection.Copy
    Range("B2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

This copies whatever you selected and pastes only the values into cell B2. The PasteSpecial method accepts parameters like xlPasteFormats for formats, xlPasteFormulas for formulas, and Operation:=xlMultiply for math operations. You can also use SkipBlanks:=True and Transpose:=True.

A more flexible version lets you paste into the currently selected cell rather than a hard-coded address:

Sub PasteValuesToSelection()
    Selection.Copy
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

This works when you select the destination cell before running the macro.

Common Problems and How to Avoid Them

The most frequent issue with Paste Special macros is that they fail when the copy range is empty or when the destination is not a single cell. Check that you have a selection before running the macro. Another common mistake is forgetting to clear the clipboard with Application.CutCopyMode = False, which leaves the marching ants around the copied range.

If you record a macro and it doesn’t work later, it’s usually because the recorded steps reference specific cell addresses. For example, if you recorded copying A1:A10 and pasting into B1, the macro will always copy A1:A10, even if you have different data now. To make it dynamic, use Selection or ActiveCell as shown above, or use named ranges.

Macros are disabled by default in Excel. If your macro doesn’t run, check the trust center settings: go to File > Options > Trust Center > Trust Center Settings > Macro Settings and choose Disable all macros with notification. You’ll then see a prompt to enable macros when you open the workbook. Saving the file as .xlsm (macro-enabled) is required for macros to be stored.

Paste Special vs. Normal Paste: When to Use Each

Use normal paste when you want the destination to look exactly like the source, including formatting and formulas. Use Paste Special when you need only one aspect. Typical scenarios:

• Values only – when you send a report to someone and don’t want them to see or alter your formulas.
• Formats only – when you want to apply the same color scheme and fonts to a new table without changing its data.
• Multiply/Add – when you need to adjust all prices by 5% or add a fixed fee to a column.
• Transpose – when you have data in rows and want it in columns (or vice versa).
• Column widths – when you paste a table but the columns come out too narrow, and you want the source widths.

A macro is worth the setup time when you perform the same Paste Special operation more than a few times per day. For one-off tasks, the keyboard shortcut is faster than writing code.

Frequently Asked Questions

How do I paste values without formatting using a macro?

Use the PasteSpecial method with Paste:=xlPasteValues. In a recorded macro, this appears as .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False. The extra parameters are just the default settings.

Can I assign a keyboard shortcut to a Paste Special macro?

Yes. When you record a macro, you can enter a shortcut key in the Record Macro dialog (e.g., Ctrl+Shift+V). For an existing macro, go to the Macros dialog, select the macro, click Options, and set a shortcut key. Note that this overrides any built-in Excel shortcut that uses the same key combination.

Why does my macro fail with a 1004 error?

Error 1004 often means the copy range is not valid or the destination is not accessible. Common causes: you didn’t copy anything before running the macro, the destination worksheet is protected, or the range is on a different sheet that isn’t active. Add a line like If Selection.Cells.Count = 1 Then Exit Sub to avoid running on a single cell if you expect a range.

Can I use Paste Special to add a value to a range?

Yes. Copy a cell containing the value you want to add, select the target range, then use Paste Special with Add selected under Operation. In VBA, use Operation:=xlAdd. This works for numbers only; it doesn’t add text.

Is there a way to paste only the column widths without the data?

Yes. Copy the entire column (or columns) whose width you want, select the destination column, and choose Paste Special > Column widths. This pastes only the width, not the content or formatting.

Does Paste Special work the same in Excel for Mac?

Mostly, but the keyboard shortcut is Cmd+Ctrl+V instead of Ctrl+Alt+V, and the dialog looks slightly different. The VBA PasteSpecial method works the same, but some operation options like xlPasteColumnWidths may not be available in older Mac versions.

Related

• Survive Lava for Brainrots: Every Mutation & Effect
• Delete Internet Pressing Button? The Truth
• SD Card Not Showing on Mac? Fixes That Work

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2