I have reports that I run frequently from 2 different sources that both have one unique identifier that links the data across both reports (I'll call the field ID "LINK ID"). This field appears in both sets of exports (however the exports are from 2 different systems that populate different data points). I typically cut and paste each report to separate sheets within a newly created workbook and then go about matching them up.
At one point I had worked on a subroutine that would cycle through the column that contained the "LINK ID" in one sheet, and then cycle through the other column that contained the same "LINK ID" on the other worksheet, and if it found a match it would copy the entire row of data linked to that match and paste it over onto the first worksheet.
The code that I had worked on never got completely finished, but I am trying to see if I can figure out this once and for all because it would be AWESOME if I didn't have to sift through these with my eyes one at a time.
Here is what I have:
Private Sub Find_And_Link()
Dim rw As Long
Dim mr2 As Long
Dim ws3 As Worksheet
Set ws3 = Sheets("VM")
With Sheets("AY")
For rw = 2 To .Cells(Rows.Count, "F").End(xlUp).Row
If CBool(Application.CountIf(ws3.Columns(1), .Cells(rw, "F").Value)) Then
mrw = Application.Match(.Cells(rw, "F"), ws3.Columns(1), 0)
ws3.Cells(mrw, "A").Resize(1, 12).Copy _
Destination:=.Cells(rw, "G")
ws3.Rows(mrw).EntireRow.Delete
'getting rid of the row if match found
End If
Next rw
End With
Set ws3 = Nothing
End Sub
Here's what I was able to get. I made an assumption that your sheet "AY" is the original sheet that you want the data copied to, and "VM" is the sheet you're searching for the data to copy from (change this if it's backwards). I also changed the variable names to be a little more clear:
Sub Find_And_Link()
Dim i As Long, copyRow As Long
Dim origWS As Worksheet, copyWS As Worksheet
Set copyWS = Sheets("VM")
Set origWS = Sheets("AY")
With origWS
For i = 2 To .Cells(.Rows.Count, "F").End(xlUp).Row
If CBool(Application.CountIf(copyWS.Columns(1), .Cells(i, "F").Value)) Then
copyRow = Application.Match(.Cells(i, "F"), copyWS.Columns(1), 0)
Debug.Print "Match found in row " & copyRow & "."
copyWS.Cells(copyRow, "A").Resize(1, 12).Copy Destination:=.Cells(i, "G")
copyWS.Rows(copyRow).EntireRow.Delete 'getting rid of the row if match found
Else
Debug.Print "No match found for Link ID " & .Cells(i, 6) & " :("
End If
Next i
End With
Set copyWS = Nothing
End Sub
I tried this and it worked. Note: In "AY", your "Link IDs" are all in column F. In "VM", the "Link IDs" are all in Column A.
This will find a match in "VM", copy the data from column A to L, then paste that in "AY", column G through S. Note that it's not quite what you asked (you asked to replace the entire row in the original sheet), but I did this because it's in your code.
To instead replace the entire line in your original "AY" sheet, change the Copy destination to Destination:=.Cells(i,"A"). (You might want to add .Rows(i).Clear before that line to clear any data in that row.)