Paste Special, Text in Excel 2007

  • Thread starter Thread starter Vernon Balbert
  • Start date Start date
V

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.
 
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.
 
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
 
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.
 
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
 
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
Click to expand...

"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.
 
Hi Ron

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

--
Regards
Roger Govier



Ron de Bruin said:
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
Click to expand...
 
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
 
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
Click to expand...

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?
 
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.
 
Back
Top