Find a Cell value in another (entire)sheet if its matches than copy column B

Question

I have 2 sheets. Sheet1 conains exported emails with numbers between texts. Sheet2 contains the numbers that I looking for in Sheet1. I try to search Column A values from Sheet2 in the entire Sheet1. If its maches somewhere in row I would like to copy column B value to Sheet2 "B". I have the below code find Sheet2 Column A values in Sheet1 and highlight it and also find the value from column B but it only works If the value that I looking for is placed next to it. I need your help to change the code to always copy the values(dates) from column B.

Sheet1 with emails,

Sheet2 with the numbers that I looking for

Expected: Expected outcome

Many thanks for your help in advance.

Sub find()
Dim lastRow As Integer
Dim rng As Range

lastRow = Sheets("Sheet2").Range("A65000").End(xlUp).Row   

For i = 1 To lastRow
Set rng = Sheets("Sheet1").Range("A:L").find(Sheets("Sheet2").Cells(i, 1))

If Not rng Is Nothing Then Sheets("Sheet2").Cells(i,1).Cells.Interior.Color = vbGreen
Sheets("Sheet1").Range(rng(, 2), rng(, 2)).Copy Destination:=Sheets("Sheet2").Range("B" & i)        
End If 
Next 
End Sub

Answer 1

Here's the exact corrected code you're looking for, followed by a slightly improved overall approach that might give you some ideas. Given that you aren't familiar with the Row property, you've done a really sharp job for someone who is so new to VBA. Keep it up... great first post.

Sub find()
Dim lastRow As Integer
Dim rng As Range

lastRow = Sheets("Sheet2").Range("A65000").End(xlUp).Row

For i = 1 To lastRow
Set rng = Sheets("Sheet1").Range("A:L").find(Sheets("Sheet2").Cells(i, 1))

If Not rng Is Nothing Then
    Sheets("Sheet2").Cells(i, 1).Cells.Interior.Color = vbGreen
    Sheets("Sheet2").Cells(i, 2).Value = Sheets("Sheet1").Cells(rng.Row, 2).Value

End If
Next
End Sub

Some ideas to consider:

Sub alternateWays()
Dim lastRow As Integer, i As Long, aCell As Range
Dim rng As Range, ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")
Set aCell = ws2.Range("A1")

Do While Not IsEmpty(aCell)
    Set rng = ws1.Range("A:L").find(aCell.Value)

    If Not rng Is Nothing Then
        aCell.Cells.Interior.Color = vbGreen
        aCell.Offset(, 1).Value = ws1.Cells(rng.Row, 2).Value
    End If


    Set aCell = aCell.Offset(1, 0) 'don't forget this....
Loop

End Sub