Hello,
I used the below answer and it works but i actually need to tweak it to copy from the second sheet.
So I have sheet 1 has full data and column A matchtes column E in sheet two with a different set of date that i don't need. So I want to copy each row from sheet 1 where column A sheet one matches column E sheet two.
The issue that I'm having is that its comparing column A sheet one to Column E sheet two and copying Column A sheet one. BUT column A sheet one has duplicates where as column E sheet two has unique values.
I've been playing around with the code provided in the original answer to this question and I've had no luck!
Related:
Comparing Two Excel Sheets and copying like data to third
For Each cell In Range("C2:C" & lRow)
x = 2
Do
If cell.Value = Sheets("Sheet2").Cells(x, "E").Value Then
cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
x = x + 1
Loop Until IsEmpty(Sheets("Sheet2").Cells(x, "E"))
Next
If you only want to copy rows with unique values in Column E (Sheet2), then try the following code:-
Sub TryAgain()
Dim lr As Long
Dim fValue As Range
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Sheet2.Range("E2:E" & lr)
Set fValue = Sheet1.Columns("A:A").Find(cell.Value)
If fValue Is Nothing Then GoTo NextCell
If cell.Value = fValue.Value Then
cell.EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
NextCell:
Next cell
Sheet3.Select
End Sub
