Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 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


Copy multiple ranges with formatting at once:

Normally, Excel doesn't support the multiple copy, but, if you have Kutools for Excel, with its Copy Ranges feature, you can quickly copy multiple selected ranges and paste them to any other location as you need.

doc delete all but selected 11

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


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 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    arturo bier · 2 years ago
    On "[b]Paste external content to worksheet always match destination formatting with VBA code[/b]".
    After "[b]4. Then click OK in the Macro Options dialog, and close the Macro dialog box[/b]".
    The code would take effect only after saving/closing your worksheet.
    • To post as a guest, your comment is unpublished.
      Alex Gibbs · 2 years ago
      Tried the close and save method. Still running into the same issue that Suneet was.
  • To post as a guest, your comment is unpublished.
    Suneet · 3 years ago
    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
    • To post as a guest, your comment is unpublished.
      Dylan · 1 years ago
      I'm getting this same error
      • To post as a guest, your comment is unpublished.
        skyyang · 1 years ago
        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!
        • To post as a guest, your comment is unpublished.
          galen · 1 years ago
          That worked, but now I have to double click, or else the Kutools message comes up. Any solution?

          • To post as a guest, your comment is unpublished.
            Djoker · 1 years ago
            Works for me, just in case somebody needs it.

            Sub PasteWithDestinationFormatting()
            ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
            End Sub