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.

 

[Dryver14]

I have just had a play with this and I too could not get it to work until I put the line selection.copy at the top, It must need to follow a copy command

 

[drsarao]

You don't need to use a macro.
Excel has a built-in Paste-Values Command/Button
-- removed inline image ---

Select and put it in the command Bar.

Or try a simplified version:
Sub PasteValues()
ActiveSheet.PasteSpecial Paste:=xlPasteValues
End Sub

 

[Peter_SSs]

I have just had a play with this and I too could not get it to work until I put the line selection.copy at the top, It must need to follow a copy command

You may have mis-interpreted this bit:

Hi,
.. for cells that have been copied manually by the user in another workbook.

As I understand that (and tested as such), the user has already (manually) selected and copied some cells in a (different) workbook, then changed workbooks, made a selection then run the code. So a 'Copy' had already been done, it just wasn't done by this code.

 

[BigC]

I have just had a play with this and I too could not get it to work until I put the line selection.copy at the top, It must need to follow a copy command

In VBA, the PasteSpecial command/method can only paste from the Clipboard, so if some other action is clearing the Clipboard between the Copy and PasteSpecial actions it won't work.

So yes, you must have a Copy method on a separate code line immediately before the PasteSpecial method

 

[Peter_SSs]

In VBA, the PasteSpecial command/method can only paste from the Clipboard, so if some other action is clearing the Clipboard between the Copy and PasteSpecial actions it won't work.

So yes, you must have a Copy method on a separate code line immediately before the PasteSpecial method

I agree that the if the clipboard is empty then the code will fail with the error reported, so that is the likely problem.

I disagree with the blue bit. Putting something on the clipboard some other way (eg via a manual Copy before running the code) works fine.

 

[drsarao]

A bit of experiments brought out the following:
After a manual copy operation,

Sheet version of PasteSpecial,
ActiveSheet.PasteSpecial Paste:=xlPasteValues fails with Runtime 1004 error.

But the Range version,
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues works fine

Range version with activecell reference,
ActiveSheet.ActiveCell.PasteSpecial Paste:=xlPasteValues fails with Runtime 438 error.

But Range version using selection,
Selection.PasteSpecial Paste:=xlPasteValues works fine.


I think the last version is the most suitable single line solution in the current context.

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

 

[Peter_SSs]

But Range version using selection,
Selection.PasteSpecial Paste:=xlPasteValues works fine.


I think the last version is the most suitable single line solution in the current context.

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

Isn't that what the OP is using?

 

[drsarao]

Isn't that what the OP is using?

You are right!
I double checked Selection.PasteSpecial Paste:=xlPasteValues after a manual copy.
It is NOT erroring out in the same sheet, different sheet or even different workbook.
OP to comment.

 

[Peter_SSs]

[/B]It is NOT erroring out in the same sheet, different sheet or even different workbook.

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?).