Private Sub Worksheet_Activate()
Dim i As Long
Dim iLastRow As Long
Dim FoundClient As Range
 iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
   Range("B2:B" & iLastRow).ClearContents
 With Worksheets("сделки")
  For i = 2 To iLastRow
    Set FoundClient = .Columns("B").Find(Cells(i, "A"), , xlValues, xlWhole, , xlPrevious)
    If Not FoundClient Is Nothing Then
      Cells(i, "B") = FoundClient.Offset(, -1)
    End If
  Next
 End With
End Sub