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
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.
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:
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
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:
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.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
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.
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
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.
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!