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 |
#1
|
|||
|
|||
Linking 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 |
#2
|
|||
|
|||
Chris wrote:
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 |
#3
|
|||
|
|||
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 "Bill Manville" wrote: Chris wrote: 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 "Bill Manville" wrote: 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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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.... "Bill Manville" wrote: 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 |
#8
|
|||
|
|||
Chris wrote:
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 |
#9
|
|||
|
|||
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.... "Bill Manville" wrote: 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 |
#10
|
|||
|
|||
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 |
|
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 |