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