Skip to main content

 How to paste external content to Excel always match destination formatting?

When copy and paste data from other external applications to worksheet, Excel will paste the data with the original formatting it used by default. If you need the pasted data using the own format of your used worksheet, you should choose Match Destination Formatting option to your need when you paste. But, if you need to apply this option frequently, I can help you set this option as default when you using Ctrl +V to paste data.

Paste external content to worksheet always match destination formatting with Excel Options

Paste external content to worksheet always match destination formatting with VBA code


Paste external content to worksheet always match destination formatting with Excel Options

To make Excel paste the data with destination formatting, you can add a special command to your Quick Access Toolbar.

1. Click File > Options, and in the Excel Options dialog box:

  • (1.) Click Quick Access Toolbar in the left pane;
  • (2.) Then choose All Commands under Choose commands from drop down list;
  • (3.) And then scroll down and choose Paste and Match Destination Formatting in the list box;
  • (4.) Then click Add >> button to add this command to the Customize Quick Access Toolbar.

doc-match-destination-formatting-1

2. After adding this command, then click OK button to close this dialog box.

3. And now, the Paste and Match Destination Formatting has been added to the Customize Quick Access Toolbar, see screenshot:

doc-match-destination-formatting-2

And when you paste the data from other application, you just need to click the Paste and Match Destination Formatting button in the Quick Access Toolbar, the content will be pasted to the sheet matching the destination formatting of your worksheet.

Note: This method is not applied to Excel 2007.


Paste external content to worksheet always match destination formatting with VBA code

You can also set the Match Destination Formatting as the default paste option when you using Ctrl + V with the following VBA code.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Set Match Destination Formatting as the default paste

Sub PasteWithDestinationFormatting()
ActiveCell.PasteSpecial (xlPasteValues)
End Sub

3. And then save and close this dialog, then press Alt + F8 keys to open the Macro dialog. In the Macro dialog, select the code name you created in step 2, and click Options button, then in the appeared Macro Options dialog, enter v under Shortcut key section, see screenshot:

doc-match-destination-formatting-3

4. Then click OK in the Macro Options dialog, and close the Macro dialog box.

And now, when you paste your data from external application into the worksheet with Ctrl + V, the data will be pasted as Match Destination Formatting by default.


Related article:

How to set paste values as default paste when using Ctrl + V in Excel?


Demo: Paste external content to Excel always match destination formatting

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
On "Paste external content to worksheet always match destination formatting with VBA code". After "4. Then click OK in the Macro Options dialog, and close the Macro dialog box". The code would take effect only after saving/closing your worksheet.
This comment was minimized by the moderator on the site
Tried the close and save method. Still running into the same issue that Suneet was.
This comment was minimized by the moderator on the site
Hey, the macro for paste is not working. When I hit CTRL+v, it says: Run-time error '1004': PasteSpecial method of Range class failed
This comment was minimized by the moderator on the site
I'm getting this same error
This comment was minimized by the moderator on the site
Hello, guys,
If you want to paste the contents from website, you should apply the following VBA code.

Sub PasteWithDestinationFormatting()
Dim xRg As Range
Set xRg = Application.InputBox("Please select a cell to paste: ", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
xRg.Range("A1").Activate
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
End Sub

After pasting this code into the code module, and then go to the Macro dialog box to set the shortcut.
From now, when pasting contents from other applications, such as Word, website, a prompt box will pop out to remind you select a cell to put the pasting contents, and the contents will be pasted as Match Destination Formatting by default.

Hope it can help you! Thank you!
This comment was minimized by the moderator on the site
What a dick
This comment was minimized by the moderator on the site
That worked, but now I have to double click, or else the Kutools message comes up. Any solution?
This comment was minimized by the moderator on the site
Works for me, just in case somebody needs it.

Sub PasteWithDestinationFormatting()
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
End Sub
This comment was minimized by the moderator on the site
it works thanks
This comment was minimized by the moderator on the site
The problem I see after enabling this macro, that works nicely btw,is that my Undo button is greyed out and CTRL+z doesn't work anymore...
This comment was minimized by the moderator on the site
Same, I also cannot use CTRL+Z or and the Undo button is grayed out after using control +Z. But CTRL +V works now to allow me to copy and paste from Google Docs to Excel and have it match the destination format, saving me hundreds of clicks. Thanks!
This comment was minimized by the moderator on the site
i dont know how to thank you for this. its finally solved THANKYOU SOMUCH
This comment was minimized by the moderator on the site
OH MY GOODNESS !!!! YOU LITERALLY ARE A LIFE SAVER!
This comment was minimized by the moderator on the site
This worked for me. Thanks. You just saved me a few hundred clicks a day.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations