VBA Copy Paste Formats: Methods and Examples

In Excel VBA, copy formatting without changing cell contents by copying a source range and pasting with Paste:=xlPasteFormats. The basic pattern is sourceRange.Copy followed by targetRange.PasteSpecial Paste:=xlPasteFormats; it transfers formatting rather than values or formulas.

What “paste formats” means in VBA

Excel’s Paste Special options separate the contents of a cell from the way those contents appear. The xlPasteFormats option tells VBA to apply the source cells’ formatting to the destination. Existing destination values and formulas are normally left in place, which makes this useful for standardizing reports, applying a template, or matching newly added rows to existing ones.

Cell formatting includes properties such as number format, font, fill, borders, and alignment. A date cell, for example, can display as “Nov 2” or “11/2/2026” even though the underlying value is the same; copying formats can change that display without replacing the value. Formatting is not the same as data validation, formulas, or values, so do not expect a format-only paste to copy those features.

Conditional formatting needs particular attention. A paste operation can affect conditional-formatting rules as well as ordinary cell formatting, and the resulting rules can depend on the source, destination, and existing rules. If the appearance is driven by conditional formatting, check the destination afterward and review Home > Conditional Formatting > Manage Rules if a rule appears to be missing, duplicated, or applied to the wrong range.

Basic VBA example

This macro copies the formats from A1:C10 on Sheet1 to E1:G10 on the same worksheet. The ranges have matching dimensions, which is a reliable arrangement for a block-to-block copy.

Sub CopyFormatsToRange()
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Set the source and destination blocks.
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("E1:G10")

    ' Copy formatting only; retain destination contents.
    sourceRange.Copy
    targetRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

The final line clears Excel’s moving copy border and releases copy mode. It is not essential to the format transfer, but it leaves the interface in a cleaner state. The macro uses fully qualified worksheet and range references, so it does not rely on whichever sheet happens to be active.

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even when the macro only appears to change formatting. On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and run the macro from Developer > Macros. On Mac, use Developer > Visual Basic; Option+F11 may also work, depending on the keyboard and function-key settings.

Copying formats between worksheets or workbooks

The method also works across worksheets. Set the source and destination using their worksheet names, and keep both worksheets in the same workbook if you use ThisWorkbook. For example, a template sheet can supply formatting to a report sheet:

Sub ApplyTemplateFormats()
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Use equal-sized ranges on two worksheets.
    Set sourceRange = ThisWorkbook.Worksheets("Template").Range("A1:D20")
    Set targetRange = ThisWorkbook.Worksheets("Report").Range("A1:D20")

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

For a different open workbook, refer to that workbook explicitly rather than assuming it is active. For example, use Workbooks("MonthlyReport.xlsx").Worksheets("Report").Range("A1:D20") for the target, provided that workbook is open and the name matches. A closed workbook cannot be addressed this way without opening it first. Explicit references help prevent a macro from applying a template to the wrong file or sheet.

Choosing the source and destination ranges

For a block of cells, make the source and destination the same size and shape. A 10-row by 3-column source paired with a 10-row by 3-column target maps each source cell’s formatting to the corresponding destination cell. This is easiest to reason about when the blocks represent the same fields or report layout.

If you want to apply one cell’s appearance to a larger area, use a single-cell source and specify the destination range. Check the result in Excel, especially if the source has distinctive borders: edge and interior borders can make a repeated format look different from a complete table style. When the intended pattern is complex, creating a representative block of the same dimensions is more predictable.

Be deliberate about what the range includes. Copying an entire column’s formats can be unnecessarily broad, while copying only a header cell will not format the data rows below it. Avoid using UsedRange automatically unless that is truly the intended source; formatting applied far down a sheet can make UsedRange larger than the visible data. A defined range or a clearly specified address is often safer.

What this method does not copy

Format-only paste is not a general-purpose clone operation. It is intended to apply formatting while retaining destination contents. It does not replace formulas or values with the source’s contents, and it does not resize columns or rows to match the source. If a formatted date column needs a wider width, set the column width separately or use AutoFit on the appropriate destination columns.

Row heights also require separate handling. To copy them, select the relevant rows and use a row-height operation, or assign the destination row heights in VBA. Column widths can be copied with Excel’s Paste Special column-width option, but that is a different paste type from xlPasteFormats. Separating these tasks makes it clearer which parts of the layout a macro is meant to change.

Similarly, a format-only paste is not the right tool for transferring data validation, comments, hyperlinks, or formulas. Choose the corresponding Paste Special option or use a dedicated VBA property or method when one of those is the actual requirement. That distinction prevents an apparent “copy everything” macro from silently leaving out important cell behavior.

Alternatives and when to use them

Use xlPasteFormats when you want Excel’s normal format-paste behavior for a range. It is concise and preserves the relationship between the source and destination blocks. For a one-off manual task, the ribbon offers the same general action through Home > Paste > Paste Special > Formats; in the Paste Special dialog, choose Formats and confirm.

For a very specific change, direct formatting assignments can be clearer than copying a whole format. For example, if the only desired change is the number display, assigning the destination range’s NumberFormat from a source cell avoids changing its font, fill, borders, or alignment. Likewise, individual font, interior, or border properties can be set separately. This takes more code but reduces unintended changes when destination styling should mostly remain intact.

Another option is to use a named cell style when many cells should follow a consistent design. Styles are easier to maintain across a workbook than repeatedly copying a format from an arbitrary cell. They are particularly helpful for recurring headings and totals, although a style may not suit every exception. Choose the approach based on whether the job is to reproduce a complete format, change one property, or maintain a reusable design standard.

Common problems and fixes

The destination’s values changed. Check that the code uses Paste:=xlPasteFormats, not a normal paste or xlPasteAll. Also confirm that the macro is targeting the intended range.
The macro errors at PasteSpecial. Confirm that a copy operation immediately precedes it, that the source and target are valid ranges, and that the workbook and worksheet names are correct. A protected sheet may also prevent the intended change.
Only part of a table looks right. Check whether the source and destination shapes match and whether the selected source includes all required rows, columns, and border edges. Inspect conditional-formatting rules separately.
Column widths or row heights did not change. That is expected for a format-only paste. Handle dimensions with separate code or a suitable Excel command.
Running the macro affects the wrong sheet. Qualify every range with a workbook and worksheet reference instead of using unqualified Range, Selection, or ActiveSheet when the macro should be repeatable.

If the workbook is protected, determine whether the relevant cells or sheet can be formatted under its protection settings. Do not remove protection blindly: a macro intended to preserve data should not weaken workbook controls merely to make a paste succeed. If a workbook came from the internet, Excel may also block its macros according to security settings; only enable macros for files you trust.

Frequently Asked Questions

What is the VBA command to paste formats only?
Copy the source range, then call targetRange.PasteSpecial Paste:=xlPasteFormats. The xlPasteFormats constant specifies formatting rather than all cell contents.

Does VBA paste formats overwrite existing data?
Format-only paste is intended to change appearance without replacing destination values or formulas. Use the exact xlPasteFormats option and verify the destination range before running a macro that changes important workbook content.

Can I copy formats to another worksheet?
Yes. Set the source and destination ranges with their respective worksheet references, then copy and use PasteSpecial. Matching source and destination dimensions are usually the clearest setup.

Does PasteSpecial copy column widths?
No. xlPasteFormats does not copy row heights or column widths. Treat dimensions as a separate task and use the relevant Excel command or VBA assignment when needed.

Can I undo a VBA format paste?
Excel’s Undo command does not undo VBA macro actions. Save a backup copy before running the macro, particularly if it applies formatting to a large or important range.

How do I paste formats without using the clipboard?
For a complete format copy, Excel’s PasteSpecial approach is the standard method. If you need only one property, assign that property directly in VBA, such as the destination’s NumberFormat, rather than copying the entire cell format.

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.