Creating a Shortcut for Pasting Values

YOU DA MAN !

@James
Running a macro clears the undo stack. The only solution to this is doing it without a macro :-)

The Ctrl-" didn't work for me. I also tried Ctrl-'.
The idea of assigning the added toolbar button to the number keypad is intriguing, but I'm not sure I follow your directions.

Thanks for the tips... When I customized my Standard Toolbar I didn't notice that a PasteValues command was available. This is better than wnen I used the Paste pulldown and selected Values, but it still causes a lot of "mousing around" inefficiencies.
I created a macro with "Record a macro" but had to fine tune it in the Visual Basic editor, and it works BUT the result cannot be undone! I tried your slightly simpler macro code, but it cannot be undone either...
Alas, a lot of my PasteValues usage is to temporarily change a value to see impact elsewhere on the spreadsheet.
Any idea how to fix this limitation!?!!?

In Excel 2010 you can use Customized Ribbon and choose command Command Not in the Ribbon, then you can release Paste Value option to your excel 2010

Allen Wyatt

the following macro code does not work in Office 365 excel.
"
Sub PasteVal()
Selection.PasteSpecial Paste:=xlValues
End Sub
"

While debug it gives error in "Selection.PasteSpecial" string.

please help in this regards

Vikas

to Mark for comment on 20 Nov 2012, 11:25

Thanks a lot it helped a lot.
It worked

This is very useful macro commands to increase the working speed with Excel.

Ctrl+"
does what y,all want

Easy

Step 1: Create the toolbar button
(instructions above)
Step 2: On the number pad type '/' then the number that is assigned to the button.

You can assign it to any number by changing the order in the bar.

I use this for a quick sum all shortcut.

It is so usefull really, especially for 2003 and 2007 users. But only one problem is that I cant undo after i paste by using macro.

Same problem as Steve, all i want to do is when i ctrl-C and ctrl-v it will only paste values, and its not just for my computer, its for everyone that uses the sheet. MACROS ARE OUT OF QUESTION, scares my boss and everyone else onec they recive that virus warning.

Yes, you can do what Mark suggests. Or, you could simply click the link at the end of the tip that takes you to a version of this for later, ribbon-based versions of the program.

Steve,
To replicate the above for Excel 2010 do the following:
In the Ribbon navigate to File-> Options
In to pop-up window select Customise Ribbon
In the right hand side column select which of the main tabs you’d like your shortcut to be placed (i.e. ‘Home’ or ‘Insert’)
At the bottom of this column click ‘New Group’. This will create a new item within your chosen tab called ‘ New Group (Custom)
You may want to call it something more meaningful, like ‘Paste Values’. To do this, click on the ‘Rename’ button at the bottom. In the pop-up window type in a new name in the ‘Display name’ window (bottom of the pop-up). Click ok.
With your new group selected, move now to the left hand column. From the drop down list (Choose commands from) select ‘All Commands’
Scroll down to command called’ Values’ (the one with a numbered icon ‘123’)
Select the command and click on the ‘Add’ button in the middle of the pop-up. You now should see the command icon copied across to your newly created Group.
Click ok. Your new button should be available in the Tab you’ve selected to create it in.
Thanks

You mention "ribbons." That means you are using Excel 2007 or Excel 2010. This tip wasn't designed to work with those versions of Excel. Sorry!

The older versions of Excel (pre-ribbon) were much, much more customizable than the newer, ribbon-based versions.

-Allen

I am trying to follow your instructions for creating a toolbar button for pasting values. Your very first step is "Choose Customize from the Tools menu." However, I cannot find any "Tools" menu among the seven ribbons in Excel. If I am looking at an Excel spreadsheet with the names of the seven ribbons on top of the screen, how would I navigate to the "Tools" menu?