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

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

Hi Toppers,
Thank you for helping. Your code is giving me syntax error at :

..Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues,
Operation:=xlNone,

Any idea?

Thanks
Syed


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

Hi,

Suggests a typing error. I copied the code direct from my posting to you
and it worked OK. Did you copy or re-type?

"saziz" wrote:


Hi Toppers,
Thank you for helping. Your code is giving me syntax error at :

.Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues,
Operation:=xlNone,

Any idea?

Thanks
Syed


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

I copied
Syed


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

Hi,
It's late (UK time). If you want to post spreadsheet to me, I'll
look at it tomorrow. )


"saziz" wrote:


I copied
Syed


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

Your code is giving me syntax error at :

Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,

I bet it is, if you have a line of code ends with a comma...

..Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

is all one line, there should be no wrap to a 2nd line.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"saziz" wrote in
message ...

I copied
Syed


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

It is just a line wrap problem caused by your newsreader. Try:

Sub mycode1()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
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
Application.CutCopyMode = False
End Sub

Hope this helps
Rowan

saziz wrote:
I copied
Syed