hello
I would like to use the values of a range (same range in same worksheet
name), but from different workbooks (could be up to 40).


I am currently doing it but my code involves opening each workbook which is
cumbersome.
Once the workbook are opened the following code is applied
' Test to see if the file is open. if not opened then message
If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value)
Then
MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value &
" is not opened or the workbook name is misspell." & vbCr & vbCr & _
"Open it or check the spelling on your list (do not forget
the extension .xls)"
Range("WorkbookNameCell1").Offset(i, 0).Select
Exit Sub

End If

'take appropriate values
Dim i As Long
For i = 0 To 29
Dim Expt(1 To 8) As Range
Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i,
0).Value).Worksheets("Forecast").Range("H8:I39")
etc

In order not to open the workbooks, and because I do not know in advance
which path the user will be using,
I would like to write the path in a cell
MyPath = Range("PathName").Value
and add the path in the code, so I do not have to open each workbook.

Any help greatly appreciated. Thanks
--
caroline