This may suit your needs
It works only if all of the open workbooks are open.

Sub WorkBookLoop()
Dim intWorkBookCounter As Integer
Dim intPlaceRow As Integer

intPlaceRow = 1

For intWorkBookCounter = 1 To Workbooks.Count
Workbooks(intWorkBookCounter).Activate
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRo w, 2) =
Workbooks(intWorkBookCounter).Worksheets("Sheet1") .Cells(1, 1).Value
intPlaceRow = intPlaceRow + 1
Next intWorkBookCounter
End Sub


If having all of the workbooks open is not feasible try this solution
from John Walkenbach.

http://j-walk.com/ss/excel/tips/tip82.htm

I have used this several times and have had no trouble with it at all,
and was amazed at the speed it runs (1 application I have used it on
made about 600 semi-random queries populating a userform, and there was
no noticable difference in the time it took to read the same values from
an open workbook)
The website, does an excellent job of explaining how it works and how
to use it.


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=542052