Automating data import from multiple xls files to create 1 data source.

Hi,

I currently have 1 extract from sharepoint that 6 different departments are using to create multiple reports by adding department specific data and then creating pivot tables/charts. I would like to just take the 6 departments files and copy the relevant values into the main extract so that I can create all pivots and dashboards from a central locations at once. My goal is to create one data source and then use power pivot for my charting needs. Is there just a general vba script that I can use for this purpose? I'd like to create a macro that can automate this function vs. manually performing this task each month. Any assistance that can be provided would be greatly appreciated.

Thanks!

Check this link http://www.excelforum.com/excel-prog...der-stuck.html

Thanks! I'll take a look.

So I've taken a look at all of the examples. It appears that in the scenarios provided the data in the other sources is all formatted the same (i.e., same number of columns). So, I wanted to add to my initial posting the following information that may be important:

1)The 6 data sources all include data from the master extract but they each have varying number of columns.
2)Essentially, people are taking my extract and pulling out their departments data and then adding more columns to begin their analysis. I'd hope to create a macro that could automate the process of adding the new columns from the departmental files to the master and then also append any additional data for each row necessary.

For example, if I send out a file that contains 400 rows with column A, column B (department code), column C and column D. Users are filtering for their department using column B and extracting the 100 rows that applies to them and then adding additional segmentation data via column E, column F, etc...
In this scenario I would like for my master file to run a macro that looks across the new department file and add that information in column E and column F into the appropriate place in the 400 rows. There are essential 6 worksheets that this would need to be performed across. There are only a few columns that they all have in common.

Is this something that can be accomplished or am I attempting the wrong method.

Thanks.