Hi All,
Here is my trouble.

I have 1400 xl files (fortunately) with same # of columns. (4 startin
from 'B')
Col. B to E contains data 200 to 250 rows. Col. A is emply down unti
the end of data in Cl. b to e. at that poin 'average' written in col a


I want to:

copy the file name into Col A starting for A1 down until where it i
written 'average'.
Then copy all 4 columns up unitl where average is written and find las
row in a file name DataAll and paste.

This code will make my day.
Thank you for making my day.
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

We assume order of files does not matter;
You want to keep "average" as last entry in col A;
"Copy all 4 col......average is written" means the whole file;

1400 files x 200 rows=280,000 rows,
you exceded excel limit of 65,336 rows

"saziz" wrote in
message ...

Hi All,
Here is my trouble.

I have 1400 xl files (fortunately) with same # of columns. (4 starting
from 'B')
Col. B to E contains data 200 to 250 rows. Col. A is emply down until
the end of data in Cl. b to e. at that poin 'average' written in col a.


I want to:

copy the file name into Col A starting for A1 down until where it is
written 'average'.
Then copy all 4 columns up unitl where average is written and find last
row in a file name DataAll and paste.

This code will make my day.
Thank you for making my day.
Syed


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

Yes I knew that I will do however much I can in DataAll and then
continue by adding files. This is my intent.
Also I just want code to do for one file so that I can control it.
I am working also I am almost half way. Here is my code:

Sub mycode()
Dim mysht As Worksheet
For Each mysht In ActiveWorkbook.Worksheets
If mysht.Name = "Sheet1" Then
mysht.Range(mysht.Range("A:e").Find("Ave")(1), _
mysht.Range("A:e").End(xlUp)).Resize(1, 4).Copy _
Worksheets("DataAll").Range("A:E").End(xlUp)(1)

End If
Next mysht

This is copying only the last line where "Ave" is written. I want it
to find "Ave" then from there on up until row 1 (A1:E whateve3) it
should select all then copy and paste.

End Sub

thanks


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

Isn't your "average" in Range("A1").end(xldown)

and the whole lot in Range("A1").end(xldown).currentregion ?

"saziz" wrote:


Yes I knew that I will do however much I can in DataAll and then
continue by adding files. This is my intent.
Also I just want code to do for one file so that I can control it.
I am working also I am almost half way. Here is my code:

Sub mycode()
Dim mysht As Worksheet
For Each mysht In ActiveWorkbook.Worksheets
If mysht.Name = "Sheet1" Then
mysht.Range(mysht.Range("A:e").Find("Ave")(1), _
mysht.Range("A:e").End(xlUp)).Resize(1, 4).Copy _
Worksheets("DataAll").Range("A:E").End(xlUp)(1)

End If
Next mysht

This is copying only the last line where "Ave" is written. I want it
to find "Ave" then from there on up until row 1 (A1:E whateve3) it
should select all then copy and paste.

End Sub

thanks


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

Yes I only have average in A1 which could be down in 200th. row
somewhere.
Here is what I am working on:

For Each mysht In ActiveWorkbook.Worksheets
If mysht.Name = "Sheet1" Then
mysht.Range(mysht.Range("A:e").Find("Ave")(200), _
mysht.Range("A:e").End(xlUp)).Resize(, 5).Copy _
Worksheets("DataAll").Range("A:E").End(xlUp)(1)

I don't know how to make the code to DataAll Sheet --last row --
paste.
Hope someone can help me.
Thanks
Syed


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

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

Worksheets("DataAll").Range("A65536").End(xlUp).of fset(1,0).paste

"saziz" wrote in
message ...

Yes I only have average in A1 which could be down in 200th. row
somewhere.
Here is what I am working on:

For Each mysht In ActiveWorkbook.Worksheets
If mysht.Name = "Sheet1" Then
mysht.Range(mysht.Range("A:e").Find("Ave")(200), _
mysht.Range("A:e").End(xlUp)).Resize(, 5).Copy _
Worksheets("DataAll").Range("A:E").End(xlUp)(1)

I don't know how to make the code to DataAll Sheet --last row --
paste.
Hope someone can help me.
Thanks
Syed


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