So I was trying to copy data from different sheet and paste on current and I am getting this error:

"Application Defined or Object Defined" error '1004"

Can someone help me with this ?

Option Explicit
Sub finddata()

Dim fname As String
Dim FinalRow As Long
Dim i As Integer


Sheets("Report").Range("A10:N200").ClearContents
fname = Sheets("Report").Range("A4").Value
FinalRow = Sheets("Database").Range("A1000").End(xlUp).Row

For i = 3 To FinalRow
    If Sheets("Database").Cells(i, 1) = fname Then
    Sheets("Database").Range(Cells(i, 11), Cells(i, 24)).Copy  ----> Getting "Application Defined or Object Defined" error '1004" on this line
    Sheets("Report").Range("A1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i

End Sub
up vote 2 down vote accepted

The reference to Cells isn't on the same sheet as the call to Range. This is mentioned in the docs for Range.Item. It will will work if Database is the active sheet but not otherwise.

Try replacing the error line with

With Sheets("Database")
    .Range(.Cells(i, 11), .Cells(i, 24)).Copy 
End With

Note the periods before Cells.

  • Can you troubleshoot and tell me the right solution ? – Mr Miyagi Aug 25 '15 at 10:01
  • When I ran it first time it works perfectly second time it gives an error. – Mr Miyagi Aug 25 '15 at 10:02
  • Hey it worked perfectly thank you very much Sir. – Mr Miyagi Aug 25 '15 at 10:04

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.