Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old June 27th 05, 06:52 PM
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!


  #12   Report Post  
Old June 28th 05, 07:38 AM
Bill Manville
 
Posts: n/a
Default

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   Report Post  
Old June 28th 05, 02:27 PM
Frank Rudd via OfficeKB.com
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Workbooks Barbara Excel Worksheet Functions 2 March 21st 05 03:49 AM
Linking Workbooks Dede McEachern Excel Worksheet Functions 0 January 21st 05 08:27 PM
Linking 2 Excel Sheets, Anticipating Lines. Matthew Excel Worksheet Functions 1 December 17th 04 01:55 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 08:13 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 07:43 AM


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017