I have also tried this code. It give me error 400.

Sub mycode1()

Dim rRng As Range

rRng
Worksheets(Sheet1.Range("A:E").Find("Ave")(A).End( xlUp)).Resize(
5).Copy
Application.Goto Worksheets("DataAll")
Set rRange = Range("a1:A" & Range("A" & Rows.Count).End(xlUp).Row)

withrRange.Offset(rRng.Count, 0).Resize(1, 1).Select
ActiveSheet.paste
Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False

End Sub

Can someone please check it out.
Thank you.
Sye

--
sazi
-----------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...nfo&userid=635
View this thread: http://www.excelforum.com/showthread.php?threadid=49361