PasteSpecial method of Range class failed

[stuartpurdie]

I'm trying to set up a toolbar button to paste formulas only. I want to be able to copy a cell, select a destination and then hit the button to paste the formula.

I edited a recorded macro to leave: -

Sub mcrPasteFormulas()
'
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= False, Transpose:=False
'
End Sub

When I copy the cell, select the destination then run the macro I get a "Runtime error '1004': PasteSpecial method of Range class failed".

Can anyone tell me what's going wrong?

 

[Tom Urtis]

Yes, what's going wrong is that you didn't copy anything, at least not as far as the macro is concerned. Precede your PasteSpecial line with the cell range you want to copy, such as
Range("A1").Copy

or, copy a cell and while in copy mode run the macro.

Otherwise, VBA sees nothing on the clipboard.

 

[stuartpurdie]

Thanks Tom but I am copying a cell before running the macro and it still doesn't work.

 

[Tom Urtis]

It works when I do it. There's something else going on here. Maybe the code is not in the module of the button (or attached to the Forms button) you are clicking. Maybe you really are not in copy mode. Maybe the workbook is in design mode if it's a command button. Maybe there's no formula in the cell(s) being copied. Maybe there is other code in the workbook, such as a selection event, that is clearing the clipboard.

There are all sorts of possibilities; it's hard to tell from a distance. On the face of your description, everything should work as it's been presented. Take a close look at other VBA modules in your workbook, especially including the workbook and worksheet modules, to see if any conflicting or interfering code exists. Also, tell us *exactly* what kind of button or object you are clicking, and under what conditions, and exactly what you are doing up to that point of the mouse click.

 

[gziro]

I have somewhat similar problem...

I try to insert reference in cells in the named range. The code uses Selection.FillDown and Selection.FillRight methods, but... I want the destinatioon range to have the original formatting (that is lost after applying these methods).
So I tried to copy the original formatting, then insert cell references and afterwards reapply the original formatting... but I got this error "PastSpecial Method of Range class failed".

Seems there was nothing to copy because there were other code lines between *copy and *PastSpecial?

IF so, can you please advise how can I restore the original formatting of the destination range?

Below is the code I used:

Windows("Book2.xlsx").Activate
Range("LinkDestination").Select

Selection.Copy

ActiveCell.FormulaR1C1 = "=[Book1.xlsm]Sheet1!RC"
Selection.FillDown
Selection.FillRight

Range("LinkDestination").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Norie]

This is actually quite a different question.

The original is about using a custom command button on a toolbar.

Anyway, I think the problem could be that you select the named range again just before you do the paste special or it might be earlier in the code.

That, and othere problems, could be avoided by not using select in the first place.

What is it you are actually trying to copy?

That's not clear - you copy a range, don't really do anything with it, put a cell in the active cell then fill down/right.

 

[d0odadiddy]

Please be advised

Caution Writing PasteSpecial Macros

 

[deadheaddad]

Please be advised

Caution Writing PasteSpecial Macros

Thank you for this! I had put Application.DisplayStatusBar=True between the copy command and the paste command, which it turns out was clearing the clipboard. No error when I Debug>Compiled, but got the error when I ran it.