Linking two sheets in different books

Hi All,

Apologies if this has been asked 1000 times already ...

I have two Excel files
file1.xls
file2.xls

File1 gets auto-generated each night and populated with the latest data.

I wish to create several graphs that use all the data in file1.xls but can't
create in file1 as it gets rewritten each night.

Several years ago I believe I did something where I entered a formula in
cell A1 of Sheet1 of file2.xls such that it pulled the entire contents of
Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not figure
out how I did this years ago and all the references I have found looking
around seem to only talk about bringing over only a few cells. For example:
http://office.microsoft.com/en-us/as...984241033.aspx

The full range of data in file1.xls is not consistent with each run so I can
not just hard code a specific range of cells. I just want to pull in the
entire sheet from the first file into the second file and have it be a
reference that gets refreshed with each opening.

Can anyone offer any suggestions on this? Please let me know if this is not
explained well enough or if I need to provide more details.

Many thanks,

--
Glenn

Glenn,

Open both the workbooks, and run this little macro:

Sub Glenn()
Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
Workbooks("File2.xls").Worksheets("Sheet1").Cells
End Sub

This will, of course, remove any old data or formulas on Sheet1 of File2,
but won't affect any formulas on other sheets of File1. Those formulas
should probably be written to look at entire columns, to account for the
variable lengths.

HTH,
Bernie
MS Excel MVP

"Glenn Mulno" <[email protected]> wrote in message
news:%[email protected]...
> Hi All,
>
> Apologies if this has been asked 1000 times already ...
>
> I have two Excel files
> file1.xls
> file2.xls
>
> File1 gets auto-generated each night and populated with the latest data.
>
> I wish to create several graphs that use all the data in file1.xls but
can't
> create in file1 as it gets rewritten each night.
>
> Several years ago I believe I did something where I entered a formula in
> cell A1 of Sheet1 of file2.xls such that it pulled the entire contents of
> Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not figure
> out how I did this years ago and all the references I have found looking
> around seem to only talk about bringing over only a few cells. For
example:
> http://office.microsoft.com/en-us/as...984241033.aspx
>
> The full range of data in file1.xls is not consistent with each run so I
can
> not just hard code a specific range of cells. I just want to pull in the
> entire sheet from the first file into the second file and have it be a
> reference that gets refreshed with each opening.
>
> Can anyone offer any suggestions on this? Please let me know if this is
not
> explained well enough or if I need to provide more details.
>
> Many thanks,
>
> --
> Glenn
>
>

Thanks Bernie. One follow up:

This works fine but only when both documents are open (as you mentioned). I
can't seem to figure out how to make it work for when the first document is
still closed. To the end users - they will not even know of the existence
of the first source file.

Once upon a time (8+ years ago at another company), I did this by entering a
single formula in cell A1 of the destination file and it automatically
updated when the file opened. I can't remember what I did and can't find any
reference to it so I don't know if I was dreaming or if the support for this
was taken away years ago.

Anyway - Thanks again for the response. Any help on getting this to happen
with the first doc still closed would be great.

--
Glenn


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Glenn,
>
> Open both the workbooks, and run this little macro:
>
> Sub Glenn()
> Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
> Workbooks("File2.xls").Worksheets("Sheet1").Cells
> End Sub
>
> This will, of course, remove any old data or formulas on Sheet1 of File2,
> but won't affect any formulas on other sheets of File1. Those formulas
> should probably be written to look at entire columns, to account for the
> variable lengths.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Glenn Mulno" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi All,
> >
> > Apologies if this has been asked 1000 times already ...
> >
> > I have two Excel files
> > file1.xls
> > file2.xls
> >
> > File1 gets auto-generated each night and populated with the latest data.
> >
> > I wish to create several graphs that use all the data in file1.xls but
> can't
> > create in file1 as it gets rewritten each night.
> >
> > Several years ago I believe I did something where I entered a formula in
> > cell A1 of Sheet1 of file2.xls such that it pulled the entire contents
of
> > Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not
figure
> > out how I did this years ago and all the references I have found looking
> > around seem to only talk about bringing over only a few cells. For
> example:
> > http://office.microsoft.com/en-us/as...984241033.aspx
> >
> > The full range of data in file1.xls is not consistent with each run so I
> can
> > not just hard code a specific range of cells. I just want to pull in
the
> > entire sheet from the first file into the second file and have it be a
> > reference that gets refreshed with each opening.
> >
> > Can anyone offer any suggestions on this? Please let me know if this is
> not
> > explained well enough or if I need to provide more details.
> >
> > Many thanks,
> >
> > --
> > Glenn
> >
> >
>
>

Glenn,

You weren't dreaming.

Open up both books, and in the one that you want to have open, go to cell A1
of sheet1. Type in an = then navigate to the other workbook, and select
cell A1 of sheet1. Press enter, and you should have a link formula. Copy
that formula to all the other cells of the sheet, but only as many as you
reasonably expect to need, otherwise the file will become too large.

Then close the book that you want to have closed. The formulas with then
reference the closed book. Save and close the book with the formula. Then
overwrite the other book (give the new fiel the same name), and when you
open up the book with the formulas, they - the formulas - will automatically
reference the new workbook.

HTH,
Bernie
MS Excel MVP

"Glenn Mulno" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bernie. One follow up:
>
> This works fine but only when both documents are open (as you mentioned).
> I
> can't seem to figure out how to make it work for when the first document
> is
> still closed. To the end users - they will not even know of the existence
> of the first source file.
>
> Once upon a time (8+ years ago at another company), I did this by entering
> a
> single formula in cell A1 of the destination file and it automatically
> updated when the file opened. I can't remember what I did and can't find
> any
> reference to it so I don't know if I was dreaming or if the support for
> this
> was taken away years ago.
>
> Anyway - Thanks again for the response. Any help on getting this to happen
> with the first doc still closed would be great.
>
> --
> Glenn
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Glenn,
>>
>> Open both the workbooks, and run this little macro:
>>
>> Sub Glenn()
>> Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
>> Workbooks("File2.xls").Worksheets("Sheet1").Cells
>> End Sub
>>
>> This will, of course, remove any old data or formulas on Sheet1 of File2,
>> but won't affect any formulas on other sheets of File1. Those formulas
>> should probably be written to look at entire columns, to account for the
>> variable lengths.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Glenn Mulno" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Hi All,
>> >
>> > Apologies if this has been asked 1000 times already ...
>> >
>> > I have two Excel files
>> > file1.xls
>> > file2.xls
>> >
>> > File1 gets auto-generated each night and populated with the latest
>> > data.
>> >
>> > I wish to create several graphs that use all the data in file1.xls but
>> can't
>> > create in file1 as it gets rewritten each night.
>> >
>> > Several years ago I believe I did something where I entered a formula
>> > in
>> > cell A1 of Sheet1 of file2.xls such that it pulled the entire contents
> of
>> > Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not
> figure
>> > out how I did this years ago and all the references I have found
>> > looking
>> > around seem to only talk about bringing over only a few cells. For
>> example:
>> > http://office.microsoft.com/en-us/as...984241033.aspx
>> >
>> > The full range of data in file1.xls is not consistent with each run so
>> > I
>> can
>> > not just hard code a specific range of cells. I just want to pull in
> the
>> > entire sheet from the first file into the second file and have it be a
>> > reference that gets refreshed with each opening.
>> >
>> > Can anyone offer any suggestions on this? Please let me know if this
>> > is
>> not
>> > explained well enough or if I need to provide more details.
>> >
>> > Many thanks,
>> >
>> > --
>> > Glenn
>> >
>> >
>>
>>
>
>

This pulls in more than I would want. If a cell is empty then I end up with
a 0 in a cell that was just empty on the source sheet. I want to pull in an
exact replica of what is in the other sheet - formatting and all.

Oh well - Thanks anyway!

--
Glenn

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Glenn,
>
> You weren't dreaming.
>
> Open up both books, and in the one that you want to have open, go to cell
A1
> of sheet1. Type in an = then navigate to the other workbook, and select
> cell A1 of sheet1. Press enter, and you should have a link formula. Copy
> that formula to all the other cells of the sheet, but only as many as you
> reasonably expect to need, otherwise the file will become too large.
>
> Then close the book that you want to have closed. The formulas with then
> reference the closed book. Save and close the book with the formula.
Then
> overwrite the other book (give the new fiel the same name), and when you
> open up the book with the formulas, they - the formulas - will
automatically
> reference the new workbook.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Glenn Mulno" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bernie. One follow up:
> >
> > This works fine but only when both documents are open (as you
mentioned).
> > I
> > can't seem to figure out how to make it work for when the first document
> > is
> > still closed. To the end users - they will not even know of the
existence
> > of the first source file.
> >
> > Once upon a time (8+ years ago at another company), I did this by
entering
> > a
> > single formula in cell A1 of the destination file and it automatically
> > updated when the file opened. I can't remember what I did and can't find
> > any
> > reference to it so I don't know if I was dreaming or if the support for
> > this
> > was taken away years ago.
> >
> > Anyway - Thanks again for the response. Any help on getting this to
happen
> > with the first doc still closed would be great.
> >
> > --
> > Glenn
> >
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:[email protected]...
> >> Glenn,
> >>
> >> Open both the workbooks, and run this little macro:
> >>
> >> Sub Glenn()
> >> Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
> >> Workbooks("File2.xls").Worksheets("Sheet1").Cells
> >> End Sub
> >>
> >> This will, of course, remove any old data or formulas on Sheet1 of
File2,
> >> but won't affect any formulas on other sheets of File1. Those formulas
> >> should probably be written to look at entire columns, to account for
the
> >> variable lengths.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >> "Glenn Mulno" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Hi All,
> >> >
> >> > Apologies if this has been asked 1000 times already ...
> >> >
> >> > I have two Excel files
> >> > file1.xls
> >> > file2.xls
> >> >
> >> > File1 gets auto-generated each night and populated with the latest
> >> > data.
> >> >
> >> > I wish to create several graphs that use all the data in file1.xls
but
> >> can't
> >> > create in file1 as it gets rewritten each night.
> >> >
> >> > Several years ago I believe I did something where I entered a formula
> >> > in
> >> > cell A1 of Sheet1 of file2.xls such that it pulled the entire
contents
> > of
> >> > Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not
> > figure
> >> > out how I did this years ago and all the references I have found
> >> > looking
> >> > around seem to only talk about bringing over only a few cells. For
> >> example:
> >> > http://office.microsoft.com/en-us/as...984241033.aspx
> >> >
> >> > The full range of data in file1.xls is not consistent with each run
so
> >> > I
> >> can
> >> > not just hard code a specific range of cells. I just want to pull in
> > the
> >> > entire sheet from the first file into the second file and have it be
a
> >> > reference that gets refreshed with each opening.
> >> >
> >> > Can anyone offer any suggestions on this? Please let me know if this
> >> > is
> >> not
> >> > explained well enough or if I need to provide more details.
> >> >
> >> > Many thanks,
> >> >
> >> > --
> >> > Glenn
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Glenn,

You can't pull in formatting and truly empty cells unless you use a macro.

You could modify my original macro to open the workbook:

Sub Glenn2()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Workbooks.Open "C:\FolderPath\File1.xls"
Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
Workbooks("File2.xls").Worksheets("Sheet1").Cells
Workbooks("File1.xls").Close False
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Glenn Mulno" <[email protected]> wrote in message
news:%[email protected]...
> This pulls in more than I would want. If a cell is empty then I end up
> with
> a 0 in a cell that was just empty on the source sheet. I want to pull in
> an
> exact replica of what is in the other sheet - formatting and all.
>
> Oh well - Thanks anyway!
>
> --
> Glenn
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Glenn,
>>
>> You weren't dreaming.
>>
>> Open up both books, and in the one that you want to have open, go to cell
> A1
>> of sheet1. Type in an = then navigate to the other workbook, and select
>> cell A1 of sheet1. Press enter, and you should have a link formula. Copy
>> that formula to all the other cells of the sheet, but only as many as you
>> reasonably expect to need, otherwise the file will become too large.
>>
>> Then close the book that you want to have closed. The formulas with then
>> reference the closed book. Save and close the book with the formula.
> Then
>> overwrite the other book (give the new fiel the same name), and when you
>> open up the book with the formulas, they - the formulas - will
> automatically
>> reference the new workbook.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Glenn Mulno" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks Bernie. One follow up:
>> >
>> > This works fine but only when both documents are open (as you
> mentioned).
>> > I
>> > can't seem to figure out how to make it work for when the first
>> > document
>> > is
>> > still closed. To the end users - they will not even know of the
> existence
>> > of the first source file.
>> >
>> > Once upon a time (8+ years ago at another company), I did this by
> entering
>> > a
>> > single formula in cell A1 of the destination file and it automatically
>> > updated when the file opened. I can't remember what I did and can't
>> > find
>> > any
>> > reference to it so I don't know if I was dreaming or if the support for
>> > this
>> > was taken away years ago.
>> >
>> > Anyway - Thanks again for the response. Any help on getting this to
> happen
>> > with the first doc still closed would be great.
>> >
>> > --
>> > Glenn
>> >
>> >
>> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> > news:[email protected]...
>> >> Glenn,
>> >>
>> >> Open both the workbooks, and run this little macro:
>> >>
>> >> Sub Glenn()
>> >> Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
>> >> Workbooks("File2.xls").Worksheets("Sheet1").Cells
>> >> End Sub
>> >>
>> >> This will, of course, remove any old data or formulas on Sheet1 of
> File2,
>> >> but won't affect any formulas on other sheets of File1. Those
>> >> formulas
>> >> should probably be written to look at entire columns, to account for
> the
>> >> variable lengths.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >> "Glenn Mulno" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Hi All,
>> >> >
>> >> > Apologies if this has been asked 1000 times already ...
>> >> >
>> >> > I have two Excel files
>> >> > file1.xls
>> >> > file2.xls
>> >> >
>> >> > File1 gets auto-generated each night and populated with the latest
>> >> > data.
>> >> >
>> >> > I wish to create several graphs that use all the data in file1.xls
> but
>> >> can't
>> >> > create in file1 as it gets rewritten each night.
>> >> >
>> >> > Several years ago I believe I did something where I entered a
>> >> > formula
>> >> > in
>> >> > cell A1 of Sheet1 of file2.xls such that it pulled the entire
> contents
>> > of
>> >> > Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not
>> > figure
>> >> > out how I did this years ago and all the references I have found
>> >> > looking
>> >> > around seem to only talk about bringing over only a few cells. For
>> >> example:
>> >> > http://office.microsoft.com/en-us/as...984241033.aspx
>> >> >
>> >> > The full range of data in file1.xls is not consistent with each run
> so
>> >> > I
>> >> can
>> >> > not just hard code a specific range of cells. I just want to pull
>> >> > in
>> > the
>> >> > entire sheet from the first file into the second file and have it be
> a
>> >> > reference that gets refreshed with each opening.
>> >> >
>> >> > Can anyone offer any suggestions on this? Please let me know if
>> >> > this
>> >> > is
>> >> not
>> >> > explained well enough or if I need to provide more details.
>> >> >
>> >> > Many thanks,
>> >> >
>> >> > --
>> >> > Glenn
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

YES! Thank you very much! I then wrapped it in the Workbook_Open()
function so that it happens automatically but this appears to work
wonderfully.

Thanks again!

--
Glenn


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Glenn,
>
> You can't pull in formatting and truly empty cells unless you use a macro.
>
> You could modify my original macro to open the workbook:
>
> Sub Glenn2()
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> Workbooks.Open "C:\FolderPath\File1.xls"
> Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
> Workbooks("File2.xls").Worksheets("Sheet1").Cells
> Workbooks("File1.xls").Close False
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Glenn Mulno" <[email protected]> wrote in message
> news:%[email protected]...
> > This pulls in more than I would want. If a cell is empty then I end up
> > with
> > a 0 in a cell that was just empty on the source sheet. I want to pull
in
> > an
> > exact replica of what is in the other sheet - formatting and all.
> >
> > Oh well - Thanks anyway!
> >
> > --
> > Glenn
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:[email protected]...
> >> Glenn,
> >>
> >> You weren't dreaming.
> >>
> >> Open up both books, and in the one that you want to have open, go to
cell
> > A1
> >> of sheet1. Type in an = then navigate to the other workbook, and
select
> >> cell A1 of sheet1. Press enter, and you should have a link formula.
Copy
> >> that formula to all the other cells of the sheet, but only as many as
you
> >> reasonably expect to need, otherwise the file will become too large.
> >>
> >> Then close the book that you want to have closed. The formulas with
then
> >> reference the closed book. Save and close the book with the formula.
> > Then
> >> overwrite the other book (give the new fiel the same name), and when
you
> >> open up the book with the formulas, they - the formulas - will
> > automatically
> >> reference the new workbook.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >> "Glenn Mulno" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks Bernie. One follow up:
> >> >
> >> > This works fine but only when both documents are open (as you
> > mentioned).
> >> > I
> >> > can't seem to figure out how to make it work for when the first
> >> > document
> >> > is
> >> > still closed. To the end users - they will not even know of the
> > existence
> >> > of the first source file.
> >> >
> >> > Once upon a time (8+ years ago at another company), I did this by
> > entering
> >> > a
> >> > single formula in cell A1 of the destination file and it
automatically
> >> > updated when the file opened. I can't remember what I did and can't
> >> > find
> >> > any
> >> > reference to it so I don't know if I was dreaming or if the support
for
> >> > this
> >> > was taken away years ago.
> >> >
> >> > Anyway - Thanks again for the response. Any help on getting this to
> > happen
> >> > with the first doc still closed would be great.
> >> >
> >> > --
> >> > Glenn
> >> >
> >> >
> >> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> >> > news:[email protected]...
> >> >> Glenn,
> >> >>
> >> >> Open both the workbooks, and run this little macro:
> >> >>
> >> >> Sub Glenn()
> >> >> Workbooks("File1.xls").Worksheets("Sheet1").Cells.Copy _
> >> >> Workbooks("File2.xls").Worksheets("Sheet1").Cells
> >> >> End Sub
> >> >>
> >> >> This will, of course, remove any old data or formulas on Sheet1 of
> > File2,
> >> >> but won't affect any formulas on other sheets of File1. Those
> >> >> formulas
> >> >> should probably be written to look at entire columns, to account for
> > the
> >> >> variable lengths.
> >> >>
> >> >> HTH,
> >> >> Bernie
> >> >> MS Excel MVP
> >> >>
> >> >> "Glenn Mulno" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > Hi All,
> >> >> >
> >> >> > Apologies if this has been asked 1000 times already ...
> >> >> >
> >> >> > I have two Excel files
> >> >> > file1.xls
> >> >> > file2.xls
> >> >> >
> >> >> > File1 gets auto-generated each night and populated with the latest
> >> >> > data.
> >> >> >
> >> >> > I wish to create several graphs that use all the data in file1.xls
> > but
> >> >> can't
> >> >> > create in file1 as it gets rewritten each night.
> >> >> >
> >> >> > Several years ago I believe I did something where I entered a
> >> >> > formula
> >> >> > in
> >> >> > cell A1 of Sheet1 of file2.xls such that it pulled the entire
> > contents
> >> > of
> >> >> > Sheet1 of file1.xls into sheet1 of file2.xls. However - I can not
> >> > figure
> >> >> > out how I did this years ago and all the references I have found
> >> >> > looking
> >> >> > around seem to only talk about bringing over only a few cells.
For
> >> >> example:
> >> >> > http://office.microsoft.com/en-us/as...984241033.aspx
> >> >> >
> >> >> > The full range of data in file1.xls is not consistent with each
run
> > so
> >> >> > I
> >> >> can
> >> >> > not just hard code a specific range of cells. I just want to pull
> >> >> > in
> >> > the
> >> >> > entire sheet from the first file into the second file and have it
be
> > a
> >> >> > reference that gets refreshed with each opening.
> >> >> >
> >> >> > Can anyone offer any suggestions on this? Please let me know if
> >> >> > this
> >> >> > is
> >> >> not
> >> >> > explained well enough or if I need to provide more details.
> >> >> >
> >> >> > Many thanks,
> >> >> >
> >> >> > --
> >> >> > Glenn
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Glenn Mulno wrote:
> I want to pull in an
> exact replica of what is in the other sheet - formatting and all.
>
Edit / Copy
Shift+Edit / Paste Picture Link

if you want to use the content of the pasted cell then also Edit /
Paste but position the picture over the cell.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

> YES! Thank you very much! I then wrapped it in the Workbook_Open()
> function so that it happens automatically but this appears to work
> wonderfully.
>
> Thanks again!

You're welcome - we aim to please....

Bernie