Hi,
I think you need something like this:

Sub mycode1()

Dim rRng As Range
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("B1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With

End Sub

HTH

"saziz" wrote:


I have a bunch of files. Data format is common in all files. It looks
like this:

Col.A ColB Col C Col D Col E

empty Data Data Data Data (this goes on until about
200 rows)

then Col A has "Ave" written and it averages in all other cols.

I have written a code to find "Ave" in col A. and copy data from one
row above up untill row one. Then go to sheet "DataAll" find last row
and paste the data.

Here is my code:

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 rRng = DataAll.Range("a1:A" & Range("A" &
Rows.Count).End(xlUp).Row)

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


End With


End Sub

It gives me 400 error.

Appreciate help.

Thank you
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=493922