PLEASE help with Excel links!

**starkey93** · 07-06-2012, 09:10 AM

I have several worksheets (1 to 5) which contain different quotations on each one by other colleagues. I want to create one spreadsheet (master) which has all 5 together on seperate tabs. However, the 5 worksheers are updated and added to daily. How do you put them all together on one worksheet so that they update when the original documents are added to and updated? So then we wouldnt have to copy and paste from the original spreadsheets to the master everytime. Do i need to use Macros to do this?

Hope this makes sense.
Any help would be muchly appreciated.
Thanks

**GeneralDisarray** · 07-06-2012, 09:15 AM

Are the five sheets sitting in just one folder somewhere on your network?

Easy to do, can you provide examples?

**starkey93** · 07-06-2012, 09:20 AM

they are sat in their own folder on our company network.

So, the spreadsheets are called: John Quotations, Andrew Quotations, Steve Quotations, Sam Quotations, Andi Quotations. And i want all 5 of them to be on one spreadsheet (Master Quotations) under a different tab for each but then also updates automatically from the origianals when added to

**GeneralDisarray** · 07-06-2012, 10:22 AM

Ok, give this a try (attached). You will need to enable macros, there is a button to launch the LoopDirectory macro below.

This is one way to do it.
NOTE:
**This will just grab the Sheet1 from each persons sheet, if you need more than the first worksheet this would need to be changed.
**Assumes the data sits in a table and that the table's upper-left corner is Cell A1.
**You will have to edit the macro to include your OWN PATH to the files where declare the value of the myPath variable.

Please Login or Register  to view this content.

**starkey93** · 07-06-2012, 11:07 AM

thanks, i'll give it a go and report back

**starkey93** · 07-12-2012, 03:55 AM

hello, i finally got around to trying this. I have done what i think is right on this but it keeps producing this error (see attachment for the screenshot). Any ideas on what i am doing wrong?

Thanks!

**GeneralDisarray** · 07-12-2012, 07:54 AM

Looks like your path, i think you need to remove the \ from the end.

Notice, the CurrentFileName = DIR(myPath & Application.PathSeparator & "*.xls*") This line places a back-slash between the path and file name.

Application.PathSeparator = "|" on a mac and "\" on a windows box, so you don't need the back-slash at the end of your path definition.