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
SpecialFormulasOKEsc?

No macro involved.


Gord

On Tue, 26 Aug 2008 18:36:05 -0700, GregNga
wrote:

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" wrote:

Did you first select and copy a range as instructed?

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

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


Gord

On Tue, 26 Aug 2008 12:49:01 -0700, GregNga
wrote:

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

"Gord Dibben" wrote:

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

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

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