 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Links / June 2005Linking sheets between workbooks...
I'm trying to link a sheet from one workbook to a sheet in another workbook, and to update the info when it's changed on a daily basis...can anyone help....have not been able to find out how to do this with the research I've done...Thanks > I'm trying to link a sheet from one workbook to a sheet in another workbook, > and to update the info when it's changed on a daily basis...can anyone > help....have not been able to find out how to do this with the research I've > done...Thanks Simplest way: - Have both workbooks open - in the cell to contain the link, type = - Window to the other workbook and select the source cell - hit Enter Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup Thanks for the reply Bill....however, I'm not trying to link specific cells, I'm trying to link entire sheets.... ex: I have 4 workbooks containing several sheets of data in each which I update daily....then I have another workbook (main) that has 4 sheets, which are copies of 1 of the sheets from each of those 4 workbooks.... what I'm trying to do is link the individual sheets I need from the 4 workbooks into the workbook that needs those 4 sheets only (main), in it....so when I open the 1 (main), it updates what I've changed in the other 4... I hope this makes sense.....the way that I have been doing it is copying from 1 of the 4 and pasting it into the main...is there a way to do what I want....Thanks for any help... Chris > > I'm trying to link a sheet from one workbook to a sheet in another workbook, > > and to update the info when it's changed on a daily basis...can anyone [quoted text clipped - 10 lines] > MVP - Microsoft Excel, Oxford, England > No email replies please - respond to newsgroup I wouldn't attempt to create a full sheet worth of links. If your master workbook only contains the 4 sheets taken from the individual workbooks, why not just copy the content of the sheets? e.g. Sub Auto_Open() Dim vFile, vFile vFiles = Array("Detail1.xls", "Detail2.xls", "Detail3.xls", "Detail4.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("SomeSheet").UsedRange.Copy .Range("A1").PasteSpecial xlValues .Range("A1").PasteSpecial xlFormats End With Next End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup ok, thanks Bill...that's pretty much what I want to do....the example of code you displayed in your reply, where do I put that,...I haven't really messed with code in Excel...I'd appreciate if you can point me in the right direction....Thanks Chris > I wouldn't attempt to create a full sheet worth of links. > If your master workbook only contains the 4 sheets taken from the [quoted text clipped - 20 lines] > MVP - Microsoft Excel, Oxford, England > No email replies please - respond to newsgroup Open the workbook Alt+F11 to the VB editor Insert / Module Paste in the code You will need to adjust the names like "Detail2.xls" in the code, of course. Auto_Open will run automatically when you open the workbook. To test it you can just use Tools / Macro / Macros / Auto_Open / Run Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup ok, so Detail2 is the workbook that I'm copying from?, and where is the sheet that I want to copy identified in that code.... > Open the workbook > Alt+F11 to the VB editor [quoted text clipped - 10 lines] > MVP - Microsoft Excel, Oxford, England > No email replies please - respond to newsgroup > where is the sheet > that I want to copy identified in that code.... "SomeSheet" Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup Hi, I thought your answer might be helpful for me but I'm receiving some errors with the script.... BTW, I'm using Excel 2000. My file to hold the aggregate info is: GlobalWorksheet.xls with a tab named Global. My data is coming from unnamed tabs on worksheets named: GlobalEntry1.xls, GlobalEntry2.xls, GlobalEntry3.xls and GlobalEntry4.xls. I'm getting error "Duplicate Declaration in Current Scope". Using Dim vFile, vFiles (instead of Dim vFile, vFile) seems to get past that error. Line 3 (vFiles = Array....) returns an error but perhaps that is because the line is wrapping to a second line. When I delete the return and list the entire array text on the same line the error is not present. Line 6 returns an error and I'm not sure why. It says syntax error. The line is: With ThisWorkbook.Sheets(Left(vFile, Len(vFile)-4) If I can get past these errors perhaps the process would run ok.... > I wouldn't attempt to create a full sheet worth of links. > If your master workbook only contains the 4 sheets taken from the [quoted text clipped - 20 lines] > MVP - Microsoft Excel, Oxford, England > No email replies please - respond to newsgroup Looks like I was having a bad day on 24th March... > I'm getting error "Duplicate Declaration in Current Scope". Using Dim vFile, > vFiles (instead of Dim vFile, vFile) seems to get past that error. Correct. My typo. > Line 3 (vFiles = Array....) returns an error but perhaps that is because the > line is wrapping to a second line. When I delete the return and list the > entire array text on the same line the error is not present. Correct - forum software seems to have broken the long line for me. > Line 6 returns an error and I'm not sure why. It says syntax error. The line > is: > With ThisWorkbook.Sheets(Left(vFile, Len(vFile)-4) Should be With ThisWorkbook.Sheets(Left(vFile, Len(vFile)-4)) > 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 Hello again, > > My data is coming from unnamed tabs > > [quoted text clipped - 3 lines] > 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! 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 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? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.