Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Comparing Two Excel Sheets and copying like data to third [Closed]

Report
Posts
2
Registration date
Friday June 23, 2017
Status
Member
Last seen
July 11, 2017
 -
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 -
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!

3 replies

Answer 1 / 3
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Tyhipo,

Which code did you try? Could you please post it for us to examine.

Thank you.
vcoolio.
Answer 2 / 3
Posts
2
Registration date
Friday June 23, 2017
Status
Member
Last seen
July 11, 2017
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
Answer 3 / 3
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
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.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!