Paste Special, Text in Excel 2007

[Vernon Balbert]

I'm copying data from a web site to an Excel 2007 worksheet.
Specifically, I'm creating a database of my DVDs and using IMDB as a
source of information, specifically the release date of the movie.
Right now I'm copying the date from the web page, right-clicking on the
appropriate cell and selecting Paste Special and then selecting Text.
This is getting to be too repetitive to be convenient. Is there a
keyboard shortcut to do this? I'd love to just hit Ctrl-V (or something
similar) to paste it as plain text with no HTML codes or formatting.

 

[Roger Govier]

Hi Vernon

I assume you mean Paste Special>Values
The following code will do this for you

Sub PasteSpecialValues()

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

To copy this code to your workbook, press Alt+F11 to invoke the VB Editor.
Insert>Module
In the white pane that appears, copy the code as above
Alt+F11 to return to your Excel sheet

Press Alt+F8 to bring up Macros and with the above macro highlighted choose
Options and set whatever letter you wish to use in addition to Control to
invoke the macro.
You could use Shift+v, then Control+v would be paste, but Control+V would be
your Paste Special.

 

[Ron de Bruin]

You can also add the PasteSpecial values button to the QAT to do it with one click

Click on the arrow on the Paste button on the Home tab and then right click
on "Paste Values" and choose add to QAT

 

[Roger Govier]

Hi Ron

I hadn't realised paste Values was available as a single option to add to
the QAT until I looked at All Commands.
I could see the Paste Special in Popular commands, but that would still
require a selection of values.
Thanks for bringing this to my attention.

 

[Ron de Bruin]

Hi Roger

In Excel versions < 2007 there is also a button to do this that you can add to a toolbar

Tools>Customize
Click on Edit on the Commands tab
Drag and drop a button with 12 on it on one of your toolbars

 

[Vernon Balbert]

You can also add the PasteSpecial values button to the QAT to do it with
one click

Click on the arrow on the Paste button on the Home tab and then right click
on "Paste Values" and choose add to QAT

"Paste Values" is grayed out when I do what you suggest. Here's what's
happening and what I want:

I go to IMDB, select the text for the release date of the movie and copy
it to the clipboard. I go to Excel, right-click on the cell in which I
want to paste the data and select Paste Special. Up pops a window that
says that has a "Paste" radio button selected (all other options are
grayed out) and a pane in which are three options: HTML, Unicode Text
and Text. I should note that the cell in which I'm pasting this in is
formatted as date, not general.

 

[Roger Govier]

Hi Ron

It's amazing how blind one can be for 10 years!!!!
Many thanks

--
Regards
Roger Govier

Hi Roger

In Excel versions < 2007 there is also a button to do this that you can
add to a toolbar

Tools>Customize
Click on Edit on the Commands tab
Drag and drop a button with 12 on it on one of your toolbars

 

[Ron de Bruin]

Hi Roger

It's amazing how blind one can be for 10 years!!!!

That's the beauty of Excel.
You will never know everything

 

[Ron de Bruin]

Hi Vernon
If the clipboard is empty it is also grayed out when you copy/paste in Excel

If you copy from a webpage use the text option in the dialog you get.
The PasteSpecial values option is not working then

 

[Vernon Balbert]

Hi Vernon
If the clipboard is empty it is also grayed out when you copy/paste in
Excel

If you copy from a webpage use the text option in the dialog you get.
The PasteSpecial values option is not working then

But the question is, is there a way to just make this the default way of
pasting text, rather than having to go through the context menu?

 

[Ron de Bruin]

See Roger's answer that use a macro with the shorcut Ctrl v
You can add the macro in your personal.xlsb

See
http://www.rondebruin.nl/personal.htm

 

[Vernon Balbert]

See Roger's answer that use a macro with the shorcut Ctrl v
You can add the macro in your personal.xlsb

See
http://www.rondebruin.nl/personal.htm

Thanks to both of you!

 

[Ron de Bruin]

I add a new page on my site today that explain how you can change the image of the button.
http://www.rondebruin.nl/imageqat.htm

 

[Guest]

Great - something else I have to put into my Favorites list! For the record:
your workbook with all the icons for Command Bar Face IDs has just been a
goldmine for me.