I'm guessing that the reason you got that error is because you do not have a
sheet named "INFO". So the first thing I would try would be to change the
line

Set wks = Worksheets("INFO")

to

Set wks = Worksheets("Name of sheet you want to put your data into")

The original code is not mine, and I haven't reviewed or tested it, but the
error you're getting seems to indicate that the program can't find either a
sheet or a workbook that it is being told to look for.

Hope this helps.

Keith


"BZeyger" wrote:

When I linked ths code to a macro, I received a runtime error 9...Script out
of range.

I have 2 excel files currently open with different names. They both contain
one worksheet named "ABC". I want to get feild A1 from both the open excel
files and place them into the open INFO.xls file. Info.xls is a blank excel
file that contains the the default worksheets (sheet1, sheet2, sheet3,
sheet4). I am tring to get the info from the A! fields of the other sheet to
fill down the row of the INFO.xls file.

I am having a hrad time getting around the error message.


"Michael" wrote:

Try this:

Sub Test1()

Dim Wbk As Workbook
Dim wks As Worksheet

Set wks = Worksheets("INFO")
Set IP = wks.Range("A1")

For Each Wbk In Workbooks


If Wbk.Name < "INFO.xls" Then
Wbk.Activate
IP.Value = Wbk.Sheets("Sheet1").Range("A1").Value
MsgBox (IP.Value)
Set IP = IP.Offset(1, 0)
End If

Next Wbk


End Sub



--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"BZeyger" wrote:

I want to open a .xls file named "INFO". This is a blank .xls worksheet. I
what to run a macro that will retreive information from all open .xls files,
(names not specified).

The user would open all the .xls files they want the information from. It
could be 6 or more different .xls files at one time. The macro would cycle
through each .xls file and retreive cell "a1", for example.
I would like to put the contents of all the "a1" cells into the "INFO" sheet.

Is this possible? To retreive info from all open .xls files without knowing
the names of the files. The user just has them open.