VBA & Macro Problems

How to Set Match Destination Formatting as Default Paste in Excel via VBA

Rana Garcia Oct 19, 2026 868 views

Question details

The user wants to configure Excel to always use Match Destination Formatting as the default paste option instead of preserving the source formatting.

How to Set Match Destination Formatting as the Default Paste Option in Excel Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pasting data copied from external sources or other sheets and needing the pasted text to seamlessly adopt the font, color, and border formatting of the destination cell automatically.
Observed behavior: Excel currently retains source formatting by default and lacks a built-in configuration setting to change the permanent default paste behavior to match destination formatting.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon, and remember that workbooks containing VBA macros must be saved in the Macro-Enabled Workbook (.xlsm) format to retain the custom paste functionality.

Solution 1Recommended

Create a Custom VBA Macro and Assign it to Ctrl+V

Use a brief VBA script to override the default pasting behavior by mapping the 'Paste Values' command to the standard Ctrl+V keyboard shortcut.

Since Excel does not provide a native toggle to change the default paste behavior globally, creating a macro is the most effective workaround. By instructing Excel to paste only the values of the copied data, the destination cell's existing formatting remains fully intact.

Open the VBA Editor

Press Alt+F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor window.

Insert a New Module

In the top menu bar, select 'Insert' and then click 'Module' to open a blank coding window.

Enter the VBA Code

Copy and paste the following code into the module window: Sub PasteWithDestinationFormatting() On Error Resume Next ActiveCell.PasteSpecial Paste:=xlPasteValues End Sub

Save and Access Macros

Close the Visual Basic Editor. Back in your Excel workbook, press Alt+F8 to open the Macro dialog box.

Assign the Keyboard Shortcut

Select the 'PasteWithDestinationFormatting' macro from the list, click 'Options', enter 'v' in the Shortcut key field, and click 'OK'.

Create a Custom VBA Macro and Assign it to Ctrl+V

Shortcut Configured: You can now use Ctrl+V to run the macro and paste copied content as values, automatically allowing the destination formatting to remain in place.

Free Microsoft Office alternative

Try WPS Office for Seamless Macro Compatibility

If you are frustrated by default paste limitations, WPS Office provides a highly compatible, free alternative. It supports VBA macros natively and features highly intuitive paste options, making spreadsheet management effortless.

1. Install WPS Office: Download and install the free version of WPS Office from the official website.
2. Open Your Excel File: Launch WPS Spreadsheet and open your Macro-Enabled Excel workbook (.xlsm).
3. Enable Macros: Click 'Enable Macros' if prompted by the security warning to ensure your custom paste shortcuts function correctly.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm) and existing VBA scripts.Free, lightweight, and delivers fast performance for complex spreadsheet tasks.Familiar user interface ensures a zero-learning-curve migration.Intuitive Paste Special options readily available via easy-to-use shortcuts.

microsoft office alternative - wps office

Frequently Asked Questions

Does saving the workbook as a standard .xlsx file keep the paste macro?

No, standard .xlsx files cannot store VBA code. You must save your workbook as a Macro-Enabled Workbook (.xlsm) to ensure the custom Ctrl+V shortcut continues to work in future sessions.

Can I undo the action after pasting with the macro?

Unfortunately, running a VBA macro clears Excel's undo history. You will not be able to use Ctrl+Z to undo the paste action performed by this custom macro.

Will this custom Ctrl+V shortcut apply to all Excel workbooks?

No, the macro is specific to the workbook where it was created. To make it available across all your Excel files, you need to save the macro in your Personal Macro Workbook (Personal.xlsb).

What happens to charts or images when I use this custom Ctrl+V shortcut?

Since the macro specifically commands Excel to paste 'Values' (xlPasteValues), it will trigger an error or simply fail to paste non-text objects like charts, shapes, or images.

Excel & Data Problems