Paste special in VBA

[oerwouter]

Hi,
I want to make a macro to just paste values for cells that have been copied manually by the user in another workbook.

Sub PasteValues()

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

However I get the 1004 error: "PasteSpecial method of range class failed"

Strangest thing, because it works in another Excel file I made, I copied the code from there.

 

[oerwouter]

Good one, but I want a button with a macro because I want to disable Copy and Paste in the file, and temporaly enable it during the macro.

 

[oerwouter]

It does if the copy gets cancelled between the copy process and the macro (eg press Esc key). I suspect somehow that is happening to the OP (could be other code?).

First, thanks guys for the help!

I tried the single line solution with Selection.PasteSpecial Paste:=xlPasteValues
but I get the 1004 error. I tried it in a fresh xls-file, and I am copying from another fresh file. No other code in it and ofcourse no use of ESC key.

I am using Excel2003.

Very strange case, I have another big Excelfile where it does work. I copy the code to a new file, and it doesnt work anymore. This points to some kind of hidden file setting.

 

[faraz502]

[TABLE="width: 151"]
<tbody>[TR]
[TD]how to change date format

from


2014-04-03 15:57:00
[/TD]
[/TR]
[TR]
[TD]2014-04-03 15:49:00
[/TD]
[/TR]
</tbody>[/TABLE]

to

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: center"][TABLE="width: 97"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][TABLE="width: 129"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4/3/2014 15:57:00
[/TD]
[/TR]
[TR]
[TD="align: right"]4/3/2014 15:49:00
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"][/TD]
[/TR]
</tbody>[/TABLE]