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
...
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" wrote in message
...
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