Copy and pasting error from sheet1 to sheet2 | Excel forum

Resolved · Medium Priority · Version 2007

Wed 22 Sep 2010, 12:20 Delegate Jack said...

Jack has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Advanced course

Copy and pasting error from sheet1 to sheet2

Hi there,

Very quick one, trying to copy from sheet1 to sheet2 but vba is throwing up runtime error 1004 object defined error:

Sheets("sheet1").Range("A14:C14", Range("A14:C14").End(xlDown)).Copy Destination:=Sheets("sheet2").Range("A3")

if i remove this element the code works fine, "Range("A14:C14").End(xlDown)".

i can break the code down into seperate lines to make it work but trying to be efficient.

Thanks in advance..

Jack

Fri 24 Sep 2010, 12:50 Trainer Anthony said...

RE: Copy and pasting error from sheet1 to sheet2

Hi Jack, thanks for your query. A couple of things come to mind. First of all have you tried the code on different versions of Windows/Office. Some run time errors of this nature are triggered by differences in the object model, but usually this is because you're trying to use a method not introduced until later version of Excel. Have a read through this thread for more information:

http://www.excelforum.com/excel-programming/507929-runtime-error-1004-pastespecial-method-of-range-class-failed.html

However, I rebuilt your line of code and got it to run...twice, before the run time error kicked in. I tried clearing the clipboard, which didn't work and after some research found this article on the MSDN:

http://support.microsoft.com/kb/905164

That's about filtered data, but still relevant I believe. I think in this case you need to sacrifice elegance for functionality!

Hope this helps,

Anthony