Comparing Two Excel Sheets and copying like data to third Closed

Question

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!

System Configuration: Windows / Chrome 58.0.3029.110

vcoolio · Answer

Hello Tyhipo,

Which code did you try? Could you please post it for us to examine.

Thank you.
vcoolio.

tyhipo · Answer

Hi Vcoolio! Sorry it took me so long but here is what I used from the other answer solution.

Sub RunMe()
Dim lRow, x As Long

Sheets("Sheet1").Select
lRow = Range("C1").End(xlDown).Row

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
    
End Sub

vcoolio · Answer

Hello Tyhipo,

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

Run the code from Sheet2.

I hope that this helps.

Cheerio,
vcoolio.

Comparing Two Excel Sheets and copying like data to third

3 responses

Similar discussions

Subscribe To Our Newsletter!