software

I have to extract a value from the same cells in over 30 workbooks. Is there
a quick way to name all the workbooks in an array and state the array in the
cell formula?


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(quot;Sheet4quot;).Cells(intPlaceRo w, 2) =
Workbooks(intWorkBookCounter).Worksheets(quot;Sheet1quot;) .Cells(1, 1).Value
intPlaceRow = intPlaceRow 1
Next intWorkBookCounter
End SubIf having all of the workbooks open is not feasible try this solution
from John Walkenbach.

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: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=542052Thanks, but I was wondering if there was a method for the shortening the
worksheet formula?

quot;bgeierquot; wrote:

gt;
gt; This may suit your needs
gt; It works only if all of the open workbooks are open.
gt;
gt; Sub WorkBookLoop()
gt; Dim intWorkBookCounter As Integer
gt; Dim intPlaceRow As Integer
gt;
gt; intPlaceRow = 1
gt;
gt; For intWorkBookCounter = 1 To Workbooks.Count
gt; Workbooks(intWorkBookCounter).Activate
gt; ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells(intPlaceRo w, 2) =
gt; Workbooks(intWorkBookCounter).Worksheets(quot;Sheet1quot;) .Cells(1, 1).Value
gt; intPlaceRow = intPlaceRow 1
gt; Next intWorkBookCounter
gt; End Sub
gt;
gt;
gt; If having all of the workbooks open is not feasible try this solution
gt; from John Walkenbach.
gt;
gt; j-walk.com/ss/excel/tips/tip82.htm
gt;
gt; I have used this several times and have had no trouble with it at all,
gt; and was amazed at the speed it runs (1 application I have used it on
gt; made about 600 semi-random queries populating a userform, and there was
gt; no noticable difference in the time it took to read the same values from
gt; an open workbook)
gt; The website, does an excellent job of explaining how it works and how
gt; to use it.
gt;
gt;
gt; --
gt; bgeier
gt; ------------------------------------------------------------------------
gt; bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
gt; View this thread: www.excelforum.com/showthread...hreadid=542052
gt;
gt;


If you mean

quot;ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells(intPlaceR o w, 2) =
Workbooks(intWorkBookCounter).Worksheets(quot;Sheet1quot;) .Cells(1, 1).Valuequot;

not really, since you are looking at 2 different workbooks, you have to
tell Excel which workbook to use for what.

Check out the link from John Walkenbach, it may be easier in the long
run--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=542052Try
www.rondebruin.nl/summary2.htm--
Regards Ron de Bruin
www.rondebruin.nlquot;Newbiequot; gt; wrote in message ...
gt;I have to extract a value from the same cells in over 30 workbooks. Is there
gt; a quick way to name all the workbooks in an array and state the array in the
gt; cell formula?