Compile worksheets from multiple files into one workbook

**derekcosta** · 08-09-2011, 03:35 PM

I've looked all over the web, and I can't find a solution to my problem.

I know how to link individual cells to external files, but how do you reference an entire worksheets?

What I'm trying to create:
I am creating a sales tracker, where each rep has a file of their own. I've set up a userform to automate the entry of data on the rep files, so the data placement and format is consistent between all of the individual rep files.

I also have a master sheet that is used to generate the rep trackers themselves, so the file locations and names are all automatically set.

File locations:
Tracker\MasterTracker.xls
Tracker\Reps\Rep1.xls
Tracker\Reps\Rep2.xls
...etc.

What I can't figure out:
I have the tracker creation automation set up just fine, but what I need is for the master workbook to automatically add a worksheet/tab called Rep1, Rep2, etc, and reflect the information that's actually stored in the individual rep files.

Is this possible without manually linking each individual cell? If so, how?

Thank you so much!!

**tlafferty** · 08-09-2011, 05:53 PM

Hmmmm....
My guess is that you can write a routine to examine the path where reps books are stored, open them, create a worksheet in the master if one doesn't exist (or clear the existing tab) and make a copy of the data.

The problems with this are:
1) data won't actually be linked
2) you'd have to crawl the entire directory every time the code ran

And probably several other things.

Why not move this project to Access before you get too far into it?

**derekcosta** · 08-09-2011, 07:25 PM

I could try writing a routine, as you said, to crawl the directory and compile the data each time the master workbook is opened.

As far as Access is concerned, we don't have the available to us. I work for a very large national company, and I am at the mercy of what the IT department will allow us to have.

I was thinking that this may be a work-around solution, and I believe it's along the lines to what you suggested:

Create a form that has a list box with all of the reps names in it, and just click the reps name, and then have a load button. The form would close, and the reps information would load into the master workbook. And, for the sake of simplicity, have a summary page that is directly liked to the reps totals, showing a complete sales total for the team.

So, load the reps files individually for sales details, and have a summary sheet that is directly linked for quick numbers.

Any other ideas?

**tlafferty** · 08-09-2011, 07:34 PM

You could also share the workbook, post it in a public location and let users directly edit. Even better, if your company uses SharePoint, here's a link describing how multiple users can input to the same book.

This would be FAR preferable. Incidentally, since you work for a large company, I'd approach department management with a plan showing cost, labor/time savings by doing things this way. Couldn't hurt!

Tom