Hi brorick,
Here is the updated code for you. You didn't say if you wanted the unmatched entry to be added to sheet2, so if you do, simply switch lines 10 and 11 below (numbered on the right). Also, the comments for line 12 and lines 13-14 let you decide which method you want for duplicates (Always first row vs. next available row). Let me know if you have any more additions!
Sub UpdateEmployee()
Dim Found As Range, Look As Range, DupCell As Range, Dup As Boolean '** 1**
Set Look = Sheets(1).Range("B2") '** 2**
Set Found = Sheets(2).Columns(2).Find(What:=Look.Text, After:=[B1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False) '** 3**
Dup = True '** 4**
If Found Is Nothing Then '** 5**
Set Found = Sheets(2).Range("B1").End(xlDown).Offset(1, 0) '** 6**
Dup = False '** 7**
Msgbox "No matching record found" '** 8**
End If '** 9**
Range(Found.Offset(0, -1), Found.Offset(0, 2)) = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value '**10**
If Dup = True Then '**11**
'Uncomment 12 to always use A1:D1 on sheet 3 for duplicate values (and comment 13-14)
Sheets(3).Range("A1:D1") = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value '**12**
'Uncomment 13 and 14 to always use the next available row on sheet 3 for duplicates (comment 12)
'If Sheets(3).Range("A1") = "" Then Set DupCell = Sheets(3).Range("A1")
Else
Set DupCell = Sheets(3).Range("A65536").End(xlUp).Offset(1, 0) '**13**
'Range(DupCell, DupCell.Offset(0, 3)) = Range(Look.Offset(0, -1), Look.Offset(0, 2)).Value '**14**
End If '**15**
End Sub
Matt