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

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

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

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

"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

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....

"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

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

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

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

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!

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?

--
Message posted via http://www.officekb.com