title: Excel 2007 VBA find function. Trying to find data between two sheets and put it in a third sheet
body: <p>All,</p>
  
  <p>I am trying to write a macro to search all cells from column 2 from Sheet1 in Sheet2 and copy found rows to Sheet 2.</p>
  
  <p>This is what I have got so far:</p>
  
  <pre><code>Sub CopyUnique()
     Application.DisplayAlerts = False
  
     Set QA_14 = Sheets("QA 14Feb")
     Set Prod_14 = Sheets("Prod 14Feb")
     Set Prod_O14 = Sheets("Sheet1")
     Counter = 1
  
     Dim Found As Range
     Dim QARange As Range
     For Row = 1 To Prod_14.UsedRange.Rows.Count
  
         Set QARange = QA_14.Cells(2, 1)
         Set Found = QARange.Find(What:=Prod_14.Cells(Row, 2).Text, After:=QA_14.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  
         If Not Found Is Nothing Then
              Prod_14.UsedRange.Range(Cells(Row, 1), Cells(Row, Prod_14.UsedRange.Columns.Count)).Copy Prod_O14.Range("A" &amp; LTrim(Str(Counter)))
  
              Counter = Counter + 1
         End If
  
      Next
  
  End Sub
  </code></pre>
  
  <p>The problem occurs on the line with Find function. Just gives a type mismatch error. I have tried splitting all variables to separate line but they're not part of problem.</p>
  
  <p>Any ideas? </p>
  
  <p>Thanks</p>