by Allen Wyatt
(last updated August 31, 2019)
Avner notes that when he copies information from an external source, the default "paste" is to match the origin's format. But 99% (it is probably 99.99%) of the time, he wants to use the destination worksheet's format, so he needs to change the paste option to "Destination" format. Avner wonders if there is a way to change the default paste format to match the destination format.
Yes, there is a way to change the default using macros. However, you may want to consider a non-macro way, as well. In order to take this approach, the cleanest approach would be to add the desired paste mode to the Quick Access Toolbar. Follow these steps:
Figure 1. The Quick Access Toolbar option in the Excel Options dialog box.
Now when you want to paste, you can simply click on the newly added tool and you will have the desired effect in your worksheet.
If you want to take the macro route, you can create a very short macro like the following:
Sub PasteMyWay() On Error Resume Next ActiveCell.PasteSpecial Paste:=xlPasteValues End Sub
You can modify the value of the Paste parameter to be something different, if you find a different pasting mode is better for your purposes. Here are the Excel docs that explain the modes available:
Once your macro is set up, you can replace the regular Ctrl+V command by following these steps:
Figure 2. The Macro Options dialog box.
Now whenever you press Ctrl+V, your macro is executed, and the paste mode matches whatever you specified in the macro.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13669) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If you need to swap the contents of two cells in your worksheet, Excel provides a number of ways you can approach the ...Discover More
The AutoFill tool is very handy when it comes to quickly filling cells with a sequence of values. Sometimes, however, it ...Discover More
If you have added subtotals to your worksheet data, you might want to copy those subtotals somewhere else. This is easy ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.