Copy cell from sheet to sheet if first cell in row in Sheet1 is the same as the cel in unknown row in first column of Sheet2

[09Tina]

Hey guys, hope someone can help me.
I'm trying to write vba code to copy a cell from sheet to sheet.
I have a case where I want excel to find my typed word in cell A5 (Sheet1) on sheet2 of column 1. Then copy the row of the found word or cell next to the found word.
I wrote the code, but it doesn't work and I don't know why. This is my code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim var, i As Variant
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

sheet2lastrow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

If Target.Address = "$B$5" Then
On Error Resume Next
        For i = 2 To sheet2lastrow
           If ws1.Range("A5").Value = ws2.Range("A" & i).Value Then
                sheet2.Range("B" & i).Value = ws1.Range("$B$5").Value
            End If
        Next i
If Err <> 0 Then Exit Sub
        On Error GoTo 0
End If

End Sub

 

[Fluff]

Are you trying to copy data from sheet2 to sheet1, or the otherway round?

 

[09Tina]

I’m trying to copy data from sheet2 to sheet1.

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Fnd As Range
   
   If Target.Address(0, 0) = "A5" Then
      Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
         Cancel = True
      End If
   End If
End Sub

Double click A5

 

[09Tina]

Wow, it works. Thank you a lot!

 

[Fluff]

You're welcome & thanks for the feedback.