Hi,
I have a folder that contains Excel files. The files are numbered a0
to a1000;b1 to b2000; c1 to c3000; d1 to d4000; e1 to e5000; and f1 to
f561(A total of 5562 Excel files). I need to copy the B column 11124
row (cell) in each file and paste it in a new Excel file in one column
in the same order.(I mean the a0 file B column 11124 row cell should
be pasted in A column 1 st row cell of a new excel file; a1 file B
column 11124 row cell to be pasted in A column 2nd row cell of a new
excel file and so on... for all the 5562 files).
I would be really thanlful if you could kindly let me know the
complete code with procedure to do it. Thanks in advance. Awaiting
code procedure at the earliest possible attention.
Thanks;
Mapa.

See
http://www.rondebruin.nl/copy3.htm

Maybe you like the add-in (very easy)


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mohan" wrote in message ps.com...
Hi,
I have a folder that contains Excel files. The files are numbered a0
to a1000;b1 to b2000; c1 to c3000; d1 to d4000; e1 to e5000; and f1 to
f561(A total of 5562 Excel files). I need to copy the B column 11124
row (cell) in each file and paste it in a new Excel file in one column
in the same order.(I mean the a0 file B column 11124 row cell should
be pasted in A column 1 st row cell of a new excel file; a1 file B
column 11124 row cell to be pasted in A column 2nd row cell of a new
excel file and so on... for all the 5562 files).
I would be really thanlful if you could kindly let me know the
complete code with procedure to do it. Thanks in advance. Awaiting
code procedure at the earliest possible attention.
Thanks;
Mapa.

This is untested, but should do what you describe.
It assumes it processes all workbooks in a specific directory.
It assumes these have names like a0.xls and f561.xls

It assumes the value you want is on the first worksheet in those workbooks.

Sub copydata()
Dim sh As Worksheet
Dim sPath As String, fname As String
Dim rw As Long, s As String
Dim s1 As String
Dim bk As Workbook
Set sh = ActiveSheet
sh.Cells(1, 2).EntireColumn.Insert
sPath = "C:\Myfolder\"
fname = Dir(sPath & "*.xls")
rw = 0
Do While fname < ""
s1 = Left(fname, 1) & Format(CLng(Mid(s, 2)), "00000")
Set bk = Workbooks.Open(sPath & fname)
rw = rw + 1
sh.Cells(rw, 1).Value = bk.Worksheets(1) _
.Range("B11124").Value
sh.Cells(rw, 2).Value = s1
bk.Close SaveChanges:=False
fname = Dir()
Loop
sh.Range("A1").CurrentRegion.Sort Key1:=sh.Range("B1"), _
order1:=xlAscending, Header:=xlNo
sh.Columns(2).Delete
End Sub

--
Regards,
Tom Ogilvy



"Mohan" wrote:

Hi,
I have a folder that contains Excel files. The files are numbered a0
to a1000;b1 to b2000; c1 to c3000; d1 to d4000; e1 to e5000; and f1 to
f561(A total of 5562 Excel files). I need to copy the B column 11124
row (cell) in each file and paste it in a new Excel file in one column
in the same order.(I mean the a0 file B column 11124 row cell should
be pasted in A column 1 st row cell of a new excel file; a1 file B
column 11124 row cell to be pasted in A column 2nd row cell of a new
excel file and so on... for all the 5562 files).
I would be really thanlful if you could kindly let me know the
complete code with procedure to do it. Thanks in advance. Awaiting
code procedure at the earliest possible attention.
Thanks;
Mapa.