View Single Post
  #11   Report Post  
njtornado
 
Posts: n/a
Default

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!