Hi, I thought your answer might be helpful for me but I'm receiving some
errors with the script....

BTW, I'm using Excel 2000.
My file to hold the aggregate info is: GlobalWorksheet.xls with a tab named
Global.
My data is coming from unnamed tabs on worksheets named: GlobalEntry1.xls,
GlobalEntry2.xls, GlobalEntry3.xls and GlobalEntry4.xls.

I'm getting error "Duplicate Declaration in Current Scope". Using Dim vFile,
vFiles (instead of Dim vFile, vFile) seems to get past that error.

Line 3 (vFiles = Array....) returns an error but perhaps that is because the
line is wrapping to a second line. When I delete the return and list the
entire array text on the same line the error is not present.

Line 6 returns an error and I'm not sure why. It says syntax error. The line
is:
With ThisWorkbook.Sheets(Left(vFile, Len(vFile)-4)

If I can get past these errors perhaps the process would run ok....


"Bill Manville" wrote:

I wouldn't attempt to create a full sheet worth of links.
If your master workbook only contains the 4 sheets taken from the
individual workbooks, why not just copy the content of the sheets?

e.g.
Sub Auto_Open()
Dim vFile, vFile
vFiles = Array("Detail1.xls", "Detail2.xls", "Detail3.xls",
"Detail4.xls")
For Each vFile In vFiles
Workbooks.Open ThisWorkbook.Path & "\" & vFile
With ThisWorkbook.Sheets(Left(vFile, Len(vFile)-4)
.ClearContents ' clear sheet "Detail1" or whatever
ActiveWorkbook.Sheets("SomeSheet").UsedRange.Copy
.Range("A1").PasteSpecial xlValues
.Range("A1").PasteSpecial xlFormats
End With
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup