Error 1004, paste method of Worksheet class failed


Jim at Michoud

I often used a 2003 macro to eliminate formulae in worksheets, paste special
values, and recorded the same in 2007. It failed with the above error
message. I simplified it to 'paste' and got the same result.

What I am trying to do...
Copy a range
Select a single cell, often the same range
Run the macro to paste only the values (similar setups for addition,
subtraction, etc.)

- I only run the macro with data on the clipboard.

Since all give the same error message, I reduced it to the following code..

Sub Macro3()
' Macro3 Macro

End Sub

The macro will run and gives the proper result when I step through it.

Running it in Excel gives the error message.




Jim at Michoud

More? Let me start with less...

Create a new macro with only this command..


Flip to Excel and copy the contents of a cell or range. Select another cell.

Run the macro. I get an error. Why?

As for the more part, I maintain budget files that change monthly and must
remove, add, offset, reduce, or otherwise alter large crosstabs. I cannot
convince the folks this begs databasing, so am stuck maintaining 52K cells

I get files that must be incorporated, but only alter a small portion of the
data, thus the need for quick pastespecials: add, subtract, multiply, divide,
really simple stuff, but used quite often. So that's the background, but my
quick macros work fine in 2003, and create the error in 2007, even when
recorded from scratch.

What am I doing wrong?




when i write a macro in 2007 (compatibility mode) that has the command
it works, even repeatedly, as long as the copied cell is selected.
when the marching ants disappear from the copied cell, i get the same
error you get.
is it possible that there's something in your macro in between which
makes the copied cell unselected?
just a thought.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question