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

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

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 ? – AriKari Aug 25 '15 at 10:01
  • When I ran it first time it works perfectly second time it gives an error. – AriKari Aug 25 '15 at 10:02
  • Hey it worked perfectly thank you very much Sir. – AriKari Aug 25 '15 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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