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

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,

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

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

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

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

See George's reply: check all the code for statement with syntax error is on
one line.

(Thanks George)

"saziz" wrote:


Thanks Toppers, I will
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

Thanks Toppers, I will
Syed


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

You are right George. But I replaced it with "_"
and it took it. But when I ran it again it gave me "compile error"
expected function or variable on (red) line
Thanks
Syed

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


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

Sorry I didn't follow. What do you mean by you put it in the code? What
are you wanting it to return? Show all your code again. If you just want
the sheet name using code then somthing like this:

Range("A1").value = sheets(1).name

Regards
Rowan

saziz wrote:
Hi Rowan,
Thank you, it worked fine.
my next adventu

I have following formula for worksheet:

Mid(CELL("filename", A1), Find("]", CELL("filename", A1)) + 1, 255)

This formula copies the sheet name in cell A1. I want to put this in
the code before it finds "Ave" this is just so that I can keep track
of data coming from which sheet. I put this in the code. It seems it
did'nt like CELL function.
Any idea?
Also if it works I will change my range ".Range("B1:E" & c.Row -
1).Copy"
to
"A1:E"

Thank you
Syed

Hi Rowan,
Thank you, it worked fine.
my next adventu

I have following formula for worksheet:

Mid(CELL("filename", A1), Find("]", CELL("filename", A1)) + 1, 255)

This formula copies the sheet name in cell A1. I want to put this in
the code before it finds "Ave" this is just so that I can keep track
of data coming from which sheet. I put this in the code. It seems it
did'nt like CELL function.
Any idea?
Also if it works I will change my range ".Range("B1:E" & c.Row -
1).Copy"
to
"A1:E"

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

Try:

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Worksheets(1).Range("a2").Formula = _
"=Mid(CELL(""filename"", A1), Find(""]""," _
& "CELL(""filename"", A1)) + 1, 255)"
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastRow = .Range("B" & 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

Regards
Rowan

saziz wrote:
Rowan I am sorry for not being clear.
I am trying to insert following line:

Mid(CELL("filename", A1), Find("]", CELL("filename", A1)) + 1, 255)
This is a worksheet function.

in the same code you provided to me:

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1), Find("]",
CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastRow = .Range("B" & 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

This will give me in cell A2 the sheet name I am copying the data
from.
Just so that I can keep trak of data I am copying.
I hope its clear now. When I run this code with that function (in red)
it says "Sub or Function not defined"

Thanks
Syed

Rowan I am sorry for not being clear.
I am trying to insert following line:

Mid(CELL("filename", A1), Find("]", CELL("filename", A1)) + 1, 255)
This is a worksheet function.

in the same code you provided to me:

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1), Find("]"
CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("A1:E" & c.Row - 1).Copy
End If
End With

With Worksheets("DataAll")
lastRow = .Range("B" & 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

This will give me in cell A2 the sheet name I am copying the dat
from.
Just so that I can keep trak of data I am copying.
I hope its clear now. When I run this code with that function (in red
it says "Sub or Function not defined"

Thanks
Sye

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

You're welcome.

saziz wrote:
Hi Rowan,
Thank you so much for you help.
Everything works fine. Appreciate your help.
Thank you
syed

Hi Rowan,
Thank you so much for you help.
Everything works fine. Appreciate your 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