pulling data from multiple files without copy/paste

**redneck joe** · 07-08-2007, 02:58 PM

I have 10 workbooks that all contain info in the same format - these are sent to me from different locations around the country. The info is different, but again all in the same format - the one variable will be the number of rows containing info. One site may cave 400 records (rows) another 12, so on.

I want to combine all to one workbook.

Any way to do that without opening each one and doing a copy/paste?

**Stuart Farr** · 07-08-2007, 04:08 PM

On the first worksheet, create a link to one of the ten files. Copy the formula over the maximum range to be covered. Then highlight all the formula and copy them across to the 9 other sheets (highlight all the sheets to paste to in one go then you won't have to do it 9 times. Then select sheet 2 and do a Find 7 Replace on the filename. Repeat this process a further 8 times and then job is done.

**Stuart Farr** · 07-08-2007, 04:09 PM

Read Find & Replace not 7 !!

**redneck joe** · 07-08-2007, 06:53 PM

got the sheet part down - I'm looking to pull from individual files...

**Stuart Farr** · 07-09-2007, 08:16 AM

The formula in the cell will include the path and filename together with the sheet name and cell reference. So, this will pull from the individual files. If this is not working then there is something you're not doing correctly. If you have reviewed the formula for the first linking cell, it will likely say "=+'\\[path]\[filename]Sheetname'![Cell Reference].

If this is not clear, let me know & I'll try to explain in more detail.