Looks like I was having a bad day on 24th March...

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

Correct. My typo.

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.

Correct - forum software seems to have broken the long line for me.

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)

Should be
With ThisWorkbook.Sheets(Left(vFile, Len(vFile)-4))

My data is coming from unnamed tabs

No such thing.
All worksheets have names - maybe the tabs are not being displayed.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hello again,

My data is coming from unnamed tabs

No such thing.
All worksheets have names - maybe the tabs are not being displayed.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I should clarify... What I meant was that I did not rename the worksheets -
they have the default Excel names.

Here's the revised code:
Global.xls is the main worksheet
GlobalEntry1.xls, GlobalEntry2.xls, GlobalEntry3.xls, GlobalEntry4.xls are
used to edit the data that should go into the main worksheet.

Sub Auto_Open()
Dim vFile, vFiles
vFiles = Array("GlobalEntry1.xls", "GlobalEntry2.xls", "GlobalEntry3.xls",
"GlobalEntry4.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("Global").UsedRange.Copy
.Range("A1").PasteSpecial xlValues
.Range("A1").PasteSpecial xlFormats
End With
Next
End Sub

Now I'm getting an error " error 9 subscript out of range" for the line:
With ThisWorkbook.Sheets(Left(vFile, Len(vFile) - 4))

Any ideas?

Thanks for your help Bill!

The code as written assumes that there are sheets in the master
workbook with names which match the source workbooks, GlobalEntry1 to
GlobalEntry4 in your case.

I imagine you are getting subscript out of range because the workbook
containing the code does not contain worksheets named suitably.

The code also assumes that the source workbooks each contain a sheet
named "Global" from which you are copying the data.


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

I'm not an original poster to this thread, but I didn't know you could do
this! I can use this info right now. I assume it will work for more than 4
sheets?

--
Message posted via http://www.officekb.com