Excel VBA runtime error 1004

Question

I'm trying to write a macro to paste special formulas but keep getting a runtime error 1004 "PasteSpecial method of Range class failed".

This macro comes directly from using the "Record Macro" provided by Excel.

Sub paste_formulas()
  Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
         SkipBlanks:=False, Transpose:=False
End Sub

Here is the sequence of events:

1. Manually select a range of cells in CSV file A and copy (CTRL+C).
2. Switch to the XLS file B and select the cell were I want the copied data pasted.
3. Press Macros and run personal.xlsm!paste_formulas

That's when I get the error. Why does this work manually when I copy/paste-special formulas, but fail in the macro?

Note: I need this to work in the above sequence regardless of the selected range that I copy (will vary from time to time), and regardless of the location I paste formulas to (will also vary from time to time). In other words, hardcoding a fixed range for copy and/or paste won't work for me.

Thanks in advance for any help understanding why my code isn't working or providing a work-around.

Answer 1

The reason is very simple, When you copy from the CSV and then in your workbook, click on Macros in the Developer Toolbar, Excel clears the clipboard.

Excel has this habit of clearing the clipboard when you click on Developer | Macros. To demonstrate this, copy the cells from the same workbook. You will see the ant like border around the cells. Now in the same workbook, click on Developer | Macros. The Ant like borders will disappear :)

Set a shortcut key for your macro and use that. it will work :)