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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Under the right circumstances, you may notice problems when copying dates from one workbook to another. This tip explains ...Discover More
Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...Discover More
Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are ...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.