|
Paste Special, Text in Excel 2007
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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
Hi Vernon
I assume you mean Paste SpecialValues 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. InsertModule 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. -- Regards Roger Govier "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
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. -- Regards Roger Govier "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
Hi Roger
In Excel versions < 2007 there is also a button to do this that you can add to a toolbar ToolsCustomize Click on Edit on the Commands tab Drag and drop a button with 12 on it on one of your toolbars -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... 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. -- Regards Roger Govier "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
On 11/4/2007 3:09 AM, Ron de Bruin went clickity clack on the keyboard
and produced this interesting bit of text: 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. -- No dice, Chicago. I'm giving the orders and we're coming in. I guess the foot's on the other hand now, isn't it Kramer? |
Paste Special, Text in Excel 2007
Hi Ron
It's amazing how blind one can be for 10 years!!!! Many thanks -- Regards Roger Govier "Ron de Bruin" wrote in message ... Hi Roger In Excel versions < 2007 there is also a button to do this that you can add to a toolbar ToolsCustomize Click on Edit on the Commands tab Drag and drop a button with 12 on it on one of your toolbars -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... 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. -- Regards Roger Govier "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
Hi Roger
It's amazing how blind one can be for 10 years!!!! That's the beauty of Excel. You will never know everything -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi Ron It's amazing how blind one can be for 10 years!!!! Many thanks -- Regards Roger Govier "Ron de Bruin" wrote in message ... Hi Roger In Excel versions < 2007 there is also a button to do this that you can add to a toolbar ToolsCustomize Click on Edit on the Commands tab Drag and drop a button with 12 on it on one of your toolbars -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... 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. -- Regards Roger Govier "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
Hi Vernon
"Paste Values" is grayed out 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message .. . On 11/4/2007 3:09 AM, Ron de Bruin went clickity clack on the keyboard and produced this interesting bit of text: 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. -- No dice, Chicago. I'm giving the orders and we're coming in. I guess the foot's on the other hand now, isn't it Kramer? |
Paste Special, Text in Excel 2007
On 11/4/2007 8:54 AM, Ron de Bruin went clickity clack on the keyboard
and produced this interesting bit of text: Hi Vernon "Paste Values" is grayed out 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? -- A belly button is for salt when you eat celery in bed. |
Paste Special, Text in Excel 2007
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message . net... On 11/4/2007 8:54 AM, Ron de Bruin went clickity clack on the keyboard and produced this interesting bit of text: Hi Vernon "Paste Values" is grayed out 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? -- A belly button is for salt when you eat celery in bed. |
Paste Special, Text in Excel 2007
On 11/4/2007 9:17 AM, Ron de Bruin went clickity clack on the keyboard
and produced this interesting bit of text: 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! -- A cat assures its owner of good luck. - Chinese Proverb |
Paste Special, Text in Excel 2007
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
Paste Special, Text in Excel 2007
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. "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Vernon Balbert" wrote in message ... 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. -- "Pinky, are you pondering what I'm pondering?" "I think so, Brain. But what if the Earl of Essex doesn't like burlap pantaloons?" |
| All times are GMT +1. The time now is 10:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com