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.
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.
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.
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!
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:
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.
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:
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.