A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Linking sheets between workbooks...



 
 
Thread Tools Display Modes
  #11  
Old June 27th, 2005, 06:52 PM
njtornado
external usenet poster
 
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  
Old June 28th, 2005, 07:38 AM
Bill Manville
external usenet poster
 
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  
Old June 28th, 2005, 02:27 PM
Frank Rudd via OfficeKB.com
external usenet poster
 
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Workbooks Dede McEachern Worksheet Functions 0 January 21st, 2005 09: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 07:19 PM
Linking workbooks Fawn Worksheet Functions 1 November 11th, 2003 09:00 PM


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.