quick code needed

[saziz]

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

[PY & Associates]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[saziz]

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

[PY & Associates]

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
>
>

[saziz]

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 go to DataAll Sheet -->last row --> paste.
Hope someone can help me.
Thanks
Syed

[saziz]

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

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


End With


End Sub
Can someone please check it out.
Thank you.
Syed

[PY & Associates]

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

"saziz" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>