How to set paste values as default paste when using Ctrl + V in Excel?
Normally, in Excel, when you copy data from one range or worksheet and paste with Ctrl + V to another, the formatting will be pasted as well by default. But, sometimes, you just want to paste as values without any formatting when you paste the data. Here, I can introduce you an easy method to set the paste values only as default paste when you using Ctrl + V in Excel.
Copy multiple ranges and paste to any place 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!
When you paste the data as values only, you need to click the little square at the corner of your selection and choose Values option each time after pasting the data. The following VBA code can help you to change the default paste to paste values.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Set paste values as default paste when using Ctrl + V
Sub PasteasValue() Selection.PasteSpecial Paste:=xlPasteValues End Sub
3. Then save and close the code, and press Alt + F8 keys to open the Macro dialog box.
4. In the Macro dialog, choose the used macro name PasteasValue and then click Options button, see screenshot:
5. Then in the Macro Options dialog box, under the Shortcut key section, please enter v , see screenshot:
6. And then click OK to close the Macro Options dialog, and go on closing the Macro dialog.
And now, when you paste the data with Ctrl + V, the data will be pasted as values by default.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoSaved module - called "module 2" but nothing shows up in macros - no 'PasteasValue'. Seems like a step is missing?
- To post as a guest, your comment is unpublished.· 10 months agoI want to learn if there is a way to work in a sheet, not in a module.
If not, is there a way to work in particular sheet I determine?
- To post as a guest, your comment is unpublished.· 1 years agoIt is working but after pasting you cannot undo
- To post as a guest, your comment is unpublished.· 11 months agoSame here.
- To post as a guest, your comment is unpublished.· 11 months agoHello, guys,
I am sorry, may be there is no other good way for solving this problem, if you have any other good ideas, please comment here.
- To post as a guest, your comment is unpublished.· 10 months agoSave as binary .xlsb,
Then you can use ctrl+z with macros.
- To post as a guest, your comment is unpublished.· 1 years agoI agree Graham, just tried this in Excel 2016. When I click the Run button in the Macro dialog, I get the error message:
Run-tme error '1004':
PasteSpecial method of Range class failed
which is a shame as this really would have helped me today. I also tried Dejvid's macro below, but nothing happened when pasting from one book to another.
- To post as a guest, your comment is unpublished.· 1 years agoMake sure you have something selected in copy mode. dont even bother entering his code
- To post as a guest, your comment is unpublished.· 1 years agoUnless I missed something (and the instructions are *very* good), this does not work in Excel 2016.
- To post as a guest, your comment is unpublished.· 10 months agoI use Microsoft office Standard 2016, at it works.
- To post as a guest, your comment is unpublished.· 2 years agoThank You, Sir...
- To post as a guest, your comment is unpublished.· 2 years agonice tip, but the macro should look like this, because the code above give you an error statement, if you push ctrl+v and you didn't previously select something with ctrl+c
On Error GoTo err_handler:
- To post as a guest, your comment is unpublished.· 2 years agoThe solution from Mikael seems to nice replicate the 2013 solution offered by this page in a 2016-Excel-friendly way. Unfortunately, it also replicates the inability to undo (through Ctrl-Z or other "undo" actions) a paste action while this macro is enabled.
- To post as a guest, your comment is unpublished.· 2 years agoTo get the undo function to work after you have use a macro is quite difficult unfortunately.
Can't help you with that, sorry.
- To post as a guest, your comment is unpublished.· 3 years agoHi,
solution works great but control+z not working after pasting :( . any additional help?
- To post as a guest, your comment is unpublished.· 3 years agoThis coding does not work with the latest version of Excel 2016. Running the script gives an error code for the line Paste:=xlPasteValues
- To post as a guest, your comment is unpublished.· 2 years agoTry using this code with Excel 2016:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
- To post as a guest, your comment is unpublished.· 9 days agoExcellent solution
this code should be written directly into the VBA command box not in module box and I selected its place to be in the workbook so you make sure to END every sub opened, and ignore any message that pops up
- To post as a guest, your comment is unpublished.· 2 years agoThanks Mikael, but your code didn't work. Microsoft must have changed the VBA coding language too much in their last update. Even the text "Paste:=" results in a compiling error. If there's an answer out there, it must be in a different generation of Excel VBA.
- To post as a guest, your comment is unpublished.· 2 years agoThe macro i sent is recorded in the latest version of MS Excel 2016, so maybe the problem lies within your installation?
Besides that the sub I wrote won't work without the "Selection.PasteSpecial" part.
- To post as a guest, your comment is unpublished.· 2 years agoI just ran into the same problem.
- To post as a guest, your comment is unpublished.· 3 years agoVery nice solution. This has been plaguing me for quite some time.
Two observations ...
1) Unsurprisingly some work environments restrict or prohibit the use (and, more particularly, the emailing/receipt) of macro-enabled documents. Is there a non-macro workaround?
2) Ctrl+Z .... "Undo" will not reverse a "paste" made using this macro. Is there a workaround or additional code that would permit/enable/create "undo" for pastes made with the macro?
- To post as a guest, your comment is unpublished.· 3 years agoThank you so much :D helped a lot!