Paste Special: set text default??

[Jack Sprat]

Is it possible to set the default for the Paste command to 'Paste Text"?

I am copying some text from webpages, and the only way I can put it into a
spreadsheet without copying all the formatting, is to use Paste Special:
Text. I would like to make this the default for Paste, so that I can simply
use the keyboard shortcut, CTL+V.

Alternatively, is there a way I can assign a keyboard shortcut to Paste
Special: Text

Thanks!!

 

[Bob Umlas]

there's no paste special: text. There is paste special: Values. You can
either create a macro to ctrl/V (below) or you can put the paste
special/Values toolbar button in your standard toolbar(commandbar):
Right-click any menu or toolbar, select Customize, select the Commands tab;
in the Edit Category, about 10th item down in the Commands, is a Paste
Values command; drag that into your toolbar and it's always be there. Now a
simple click will paste values. It will be dim if there's nothing to paste.
For a macro:
Sub PasteValues()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Bob Umlas
Excel MVP

 

[Jack Sprat]

Bob.. thanks for the reply.

There definitely is a past special; text.. at least in excel 2000
see this screen capture taken on my system;
http://img.photobucket.com/albums/v19/pilaar39/blog/paste_special_text.jpg

That said, will your instructions work with excel 2000?

Thanks,
Paul

 

[Jack Sprat]

Further to my previous reply,,,
the only option I have it to place the Paste Special.. command onto the
toolbar.
See screen capture:
http://img.photobucket.com/albums/v19/pilaar39/blog/paste_special_cmd.jpg

Using it is still not that convenient (for a job with multiple needs).. as
it opens a diaglog box and requires me to select "text". I need a quick way
of pasting text, preferably with a keyboard shortcut, but will take a
toolbar shortcut that only requires one click.. can this be done??

Paul

 

[Bernard Liengme]

Bob is talking about Copy and Paste Special entirely within Excel.
You are doing Copy in a Browser and Paste Special in Excel
I know of no way to change the default as you asked
In XL2003 you can specify (after Paste) to match destination - that removes
things like hyperlinks.
If you do not have Xl2003, why not paste to Notepad first?

 

[Jack Sprat]

Ok.. thanks for the reply

Paul

Bob is talking about Copy and Paste Special entirely within Excel.
You are doing Copy in a Browser and Paste Special in Excel
I know of no way to change the default as you asked
In XL2003 you can specify (after Paste) to match destination - that
removes things like hyperlinks.
If you do not have Xl2003, why not paste to Notepad first?

 

[SeanL]

This can be done.

Use the following macro

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2006/02/28 by xxxx'
' Keyboard Shortcut: Ctrl+Shift+J
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

you can copy from your source, then select a cell where you want the data to
be pasted to. Then simply use Ctrl+Shift+J to run the macro.

Seanl

 

[SeanL]

.... did not see you mentioned you were using 2000 version, so my suggestion
may not work.

Here is how you will go about creating it. I can't remember all the
functionality in 2000, but the principles should remain the same.

1. copy some data
2. select any cell in a spreadsheet
3. got to macros - record ... enter all required fields and a shortcut key
(and then start recording)
4. use the menu items to do a paste special, and select the text option when
prompted and press ok.
5. the data should now be in your spreadsheet. Stop the recording.

now all you do is copy from your source, then select a cell where you want
the data to
be pasted to. Then simply use the hotkey you assigned to run the macro.

let me know if it worked.

 

[Jack Sprat]

This worked perfectly!!!

THANK YOU!!!

Paul

... did not see you mentioned you were using 2000 version, so my
suggestion may not work.

Here is how you will go about creating it. I can't remember all the
functionality in 2000, but the principles should remain the same.

1. copy some data
2. select any cell in a spreadsheet
3. got to macros - record ... enter all required fields and a shortcut key
(and then start recording)
4. use the menu items to do a paste special, and select the text option
when prompted and press ok.
5. the data should now be in your spreadsheet. Stop the recording.

now all you do is copy from your source, then select a cell where you want
the data to
be pasted to. Then simply use the hotkey you assigned to run the macro.

let me know if it worked.

 

[SeanL]

no problem

This worked perfectly!!!

THANK YOU!!!

Paul