I am trying to link multiple source workbooks to a master workbook. I need to repeat this process many times so I am wondering if I can use macros. The problem I seem to face is linking a source workbook to a master workbook using only their titles.
The source workbook titles are headed by using a worker's name, the job the worker was working on, and the month they are working on it. Each worker has a master workbook. Each master workbook needs to contain multiple worksheets with all of the jobs that worker had that year. Each worksheet within each master workbook needs to have 12 columns. Each column represents the revenue made by that worker on that job in that month. Each one of these scenarios has a different source workbook.
Example for John's Master Worbook: (first 3 columns of the 12 months of the year)
JobA worksheet:
Column A___________________________________________Column B___________________________________________Column C
=SUM('[John-JobA-January.xls]Worksheet3'!$A$3:$A$15)___=SUM('[John-JobA-February.xls]Worksheet3'!$A$3:$A$15)___=SUM('[John-JobA-March.xls]Worksheet3'!$A$3:$A$15
JobB worksheet:
Column A___________________________________________Column B___________________________________________Column C
=SUM('[John-JobB-January.xls]Worksheet3'!$A$3:$A$15)___=SUM('[John-JobB-February.xls]Worksheet3'!$A$3:$A$15)___=SUM('[John-JobB-March.xls]Worksheet3'!$A$3:$A$15
NOTE SEVERAL THINGS:
This is a simplified example, I just need help conceptually understanding how to organize this example and then I will be able to organize the full version.
So again there is a source workbook for each month that John worked on a job. Each job makes up a different sheet for John. And each column for each sheet represents the revenue he made on that job in that month. The revenue he made from that job in that month is summed from cells in the specific source workbook that correlates to the job and month he was working in. On a side note notice that the revenue statistics all come from the third worksheet from each and every source workbook.
So my question is how do i automate the process or create a macro where the master workbook for each worker is automatically filled in by using source workbook titles. Keep in mind John might have 50 jobs in a year so he therefore has 50 sheets that need to be filled in. If I can automate this it would save me a massive amount of time!
Thank you in advance!!
Bookmarks