I need to do a lot of cut and pasting and it irritates me that it’s pasting the formatting as well as the values.
I want just the values pasted without having to do a four million click operation…
I want to be able to do something like ctrl-c… ctrl-v
not ctrl-c… ctrl-v… mouseclick the thing… click on use formatting of destination cell.
(ok four million might have been a slight exxageration)
I used to do this with Alt-E S V. Another alternative would be to write a macro and assign it to a function key.
Alt E shows the edit menu, S for Paste Special, V for values.
What Harriet the Spry said. You can bring up paste special as a button but you don’t gain much by it. If you go the marcro route you can assign it to a button.
You do this by right clicking in the tool bar and choosing customize. Then choose the command button, and then the choose macro. You can then drag the button on the right to the tool bar and assign a macro to it. You can also change the macro button icon to suit your needs.
My version, Excel 2003 has a Paste Values button (as well as Paste Special & Paste Formatting) than can be added to any toolbar as you indicated
My 97 version also has a Paste Special button but that is not a one click operation. It gives you the options as if you hit edit and Paste Special. I just added a new button as originally described because I use the Paste Special enough to warrant a separate button. What I would advise after dragging the button up is to name the button and change the icon while customizing box is still active. To make these changes right click on the newly dragged out icon and follow the instructions. You can make the macro later. Once the icon has been dragged, named, and changed to the prefered icon (97 only gives you a smiley face to drag to the tool bar) it is ready to be linked to a macro. The first time you click on the icon it will pull up a list of macros to link to. Click on the macro created and you’re done.
FYI, once you use the customize function you’ll see all kinds of useful shortcuts such as Set Print Area that can be brought up onto the toolbar. If you have questions or need further instructions feel free to ask.
You can also assign macros to a key combination. If **Lobsang **is like me, he doesn’t want to switch between keyboarding and mousing.
These instructions are from Excel 2007, but I don’t think it’s changed much:
[ul]
[li]In the Macro name box, click the macro that you want to assign to a CTRL combination shortcut key. [/li][li]Click Options.[/li][li]In the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.[/li][/ul]
Note: The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For a list of CTRL combination shortcut keys that are already assigned in Excel, see Excel shortcut and function keys.
:smack: I didn’t scroll far enough down in my customize box. I have a Paste Values button. So now I have a separate “Paste as Text” button and a “Paste Values” button.
If you do this as a key stroke macro then I would suggest you choose the trigger carefully. Many of the keys are already assigned to usefull functions
ctrl-a Highlights everything
ctrl-n clears everything (a good one to usurp)
ctrl-c copies highlighted cells.
ctrl-v pastes copied material
ctrl-b toggles bolding of highlighted cells
ctrl-s save as
ctrl-f find
Also
ctrl-z Undo last
ctrl-y Redo last
ctrl-p Print
ctrl-: Paste today’s date
ctrl-k Add hyperlink
ctrl-i Toggles italics for highlighted cells
And probably others I don’t know about. I’d go with the Alt-E-S-V method, myself.
Not to be nit picking here but it’s ctrl-; to get the date. ctrl-: gives you the time. Which I didn’t know until I tried it.
There is a very nifty program called PureText that strips formatting information out of the clipboard. I use it mostly when editing text so everything I do doesn’t look like a ransome note, and I always wonder why RansomNoteFormat is the default.
But I bet it would fix this.
You wind up with a little PT symbol in the system tray, and whenever you click it, the clipboard gets simplified. You don’t have to interact with it in any other way.