This is what I found to work.
I figured it out, the same as you indicate.

This works for me.

The tRowCount 1 seems a good idea which I will try out.

Thanks Claus.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2,D2,F2")) Is Nothing Then Exit Sub

Dim rngFound As Range
Dim aRowCount As Long, _
aColumn As Long, _
tRowCount As Long, _
tColumn As Long

Dim myFnd As String

myFnd = Target

Application.EnableEvents = False

tColumn = Target.Offset(, 1).Column
tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents

Set rngFound = Sheets("Sheet3").Range("AA1:AL1").Find(What:=myFnd , _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

aColumn = rngFound.Column
aRowCount = Cells(Rows.Count, aColumn).End(xlUp).Row
aColumn = rngFound.Column

rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)

Else
MsgBox "No match found."
End If
Application.EnableEvents = True
End Sub