If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#8
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking Workbooks | Dede McEachern | Worksheet Functions | 0 | January 21st, 2005 08:27 PM |
linking two sheets in a third sheet | Nic | Worksheet Functions | 2 | August 1st, 2004 05:42 PM |
Importing workbooks as sheets | stillme1 | Worksheet Functions | 2 | July 1st, 2004 10:06 PM |
Excel VBA - Copying Sheets to Closed Workbooks | Ron de Bruin | Worksheet Functions | 3 | March 25th, 2004 06:19 PM |
Linking workbooks | Fawn | Worksheet Functions | 1 | November 11th, 2003 08:00 PM |