Paste unformatted text

[GregNga]

Is there a way to paste unformatted text, the equivalent of 'match
destination format' from the drop down box after doing a paste. I would like
to create a ,macro to do this ot assign a keyboard shortcut to an already
existing command, if one exists. I am trying to avoid having to click the
down arrow and select 'match destination format' evertime I do a paste

 

[Gary''s Student]

Assign a shortcut like, say CNTRL-e, to this:

Sub Macro1()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub


So you copy with CNTRL-c and paste with CNTRL-e

 

[Gord Dibben]

Sub paste_destination_format()
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

Select and copy a range, select a destination cell then run the macro.


Gord Dibben MS Excel MVP

 

[GregNga]

I tried both Gary's and Gord's examples and got runtime error '1004' (Paste
special method of rangeclass failed) on both of them.

I didn't mention that I have Excel 2003, don't know if that matters

 

[James Silverton]

GregNga wrote on Tue, 26 Aug 2008 11:42:00 -0700:

Is there a way to paste unformatted text, the equivalent of
'match destination format' from the drop down box after doing
a paste. I would like to create a ,macro to do this ot assign
a keyboard shortcut to an already existing command, if one
exists. I am trying to avoid having to click the down arrow
and select 'match destination format' evertime I do a paste

Will Steve Miller's PureText utility ( www.stevemiller.net ) do what you
want? It is a very useful method of cleaning up formatted copied text.
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Gord Dibben]

Did you first select and copy a range as instructed?

Without that the code will throw up the error you see.


Gord

 

[GregNga]

Yes, here are the steps:

1) Select cells 6A, 6B, 6C, 6D

2) With those cells highlighted, went to EDIT menu and select COPY option

3) Place cursor in cell 12A

4) Go to TOOLS menu and run MACRO. The runtime 1004 error appears

I'm sure the cells were copied succesfully in step 2 because when I do CTRL
+ V after I make the error go away. the copied cells are succesfully pasted
(they just don't have the formatting removed)

 

[Gord Dibben]

I cannot replicate the error using the macro I posted using Excel 2003.

All I get is the data pasted with destination formatting.

Do you get the effect you want if you just Copy then Paste
Special>Formulas>OK>Esc?

No macro involved.


Gord

 

[GregNga]

EDIT | PASTE SPECIAL | FORMULAS works fine. However, when I select the same
cells and run the macro, I still get the error. One thing I just noticed,
though, when I select the text from another apllication (like WORD) and copy
it to the clipboard and then go to EXCEL and run the macro, it seems to work
fine. It's when I do the copy in Excel that I have the problem. Even when I
only select one cell, I still get the error. The macro even seems to work,
when I select/copy the text from Internet Explorer, just not when I do the
same in Excel, go figure.

 

[GregNga]

Can you tell me how to assign a shortcut key to this