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 |
#11
|
|||
|
|||
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! |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
|
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 |