I am looking to do something like this

For value in Sheet1(Column A) match to value in Sheet2(Column A) and then
copy Entire Row containing match from Sheet2 only once After lastrow on
Sheet1 even if there are multiple cells with the same value on sheet1.

Try this

Sub FindItemOtherSheet()

Dim sh As Worksheet
Dim f As Range
Dim s As String

Application.ScreenUpdating = False
s = InputBox("Enter Item to find", "Hello", Default)
Set f = Worksheets("Sheet2").Columns("A:A").Find(s,
LookIn:=xlValues)
If Not f Is Nothing Then
f.Rows("1:1").EntireRow.Copy Destination:=Worksheets
("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True
Exit Sub
End If


End Sub

Thank You,

That works great but rather than using an InputBox I would like to use the
value in sheet1columnA as my search value and if it is a duplicate ignore it.

Dan

"CurlyDave" wrote:

Try this

Sub FindItemOtherSheet()

Dim sh As Worksheet
Dim f As Range
Dim s As String

Application.ScreenUpdating = False
s = InputBox("Enter Item to find", "Hello", Default)
Set f = Worksheets("Sheet2").Columns("A:A").Find(s,
LookIn:=xlValues)
If Not f Is Nothing Then
f.Rows("1:1").EntireRow.Copy Destination:=Worksheets
("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True
Exit Sub
End If


End Sub