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
'
ActiveSheet.Paste

End Sub


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

Running it in Excel gives the error message.

Help?

 

[Don Guillett]

More

 

[Jim at Michoud]

More? Let me start with less...

Create a new macro with only this command..

ActiveSheet.Paste

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
manually.

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?

 

[Susan]

when i write a macro in 2007 (compatibility mode) that has the command
ActiveSheet.Paste
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.

susan