asked on

Combining Multiple Workbooks and Worksheets

Good Morning Experts,

I've looked through many of the posts and found some things similar to what I'm trying to do, but none were exact fits or entirely close. I'm not even sure if this is possible.

Details:

I have many workbooks (about 40-50 currently) that may increase or decrease in quantity.

They all exist in a main folder called Time and each workbook exists in its own sub folder.

The folder names are all different names and contain commas EXAMPLE: Williams, Paul

The file names are also all different and contain commas EXAMPLE: Williams, Paul

Each Workbook contains 2 sheets of importance. they are: "Labor Totals" and "EQ Totals"

I've attached a sample of what the "EQ Totals" sheet looks like in the individual workbooks.

Scenario:

I am needing to combine the data from each workbooks "Labor Totals" and "EQ Totals" sheets into one master workbook. I'd like the Labor Totals to be in a sheet called "Labor Totals" in the master workbook and the"EQ Totals" to be in a sheet called "EQ Totals" in the master workbook. I'd also like to have the name of the workbook the respective data comes from added to the respective worksheet.

For example Cell A would have "Williams, Paul week ending 9-10-2001" with the data from the respective datasheet, workbook, and line after it in cells b - where ever

Question: Is this possible.

Question: Can anyone assist me in writing the code as I know nothing about macros or advanced Excel.

I'd love to make this a 1000 point question but I don't think it will be allowed

Thank you in advance.

Regards,
Brian
SP32-20111013-081205.jpg

andrewssd3

This can certainly be done, but I need a little bit more detail:

where does the week ending date come from - is this just the file name, or from somewhere else?

If all the data is to go on two sheets, would you want the Williams, Paul week ending 9-10-2001in cell A repeated for each row of the data from the individual sheet? - if you don't, filtering and totalling might be difficult

Do you want to copy all data from the EQ totals sheet the the output sheet?

It might save wasted time if you could post a sample workbook showing what you expect the output data to look like, and an input workbook with some example data (depersonalised if necessary)
Thanks

RedstoneIT

ASKER

I will set up a sample workbook and post it in a few. Thank you for the fast response on the idea feasibility.

RedstoneIT

ASKER

1. where does the week ending date come from - is this just the file name, or from somewhere else?

A. It is just part of the file name

2. If all the data is to go on two sheets, would you want the Williams, Paul week ending 9-10-2001in cell A repeated for each row of the data from the individual sheet? - if you don't, filtering and totalling might be difficult

A. yes I want the file name the data came from in each row for filtering purposes

3. Do you want to copy all data from the EQ totals sheet the the output sheet?

A. If it is easier, then yes. The key is that week to week there may be more data or less data. (Hence the nulls)

4. It might save wasted time if you could post a sample workbook showing what you expect the output data to look like, and an input workbook with some example data (depersonalised if necessary)
Thanks

A. I've included an example time sheet.

Thank you again :)

Regards,
Brian
Smith--Bill-week-ending-4-11-200.xls
Example-Master-Workbook.xlsx

RedstoneIT

ASKER

I also forgot to mention, Can the solution be functional in both 2003 and 2007 Office ?

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RedstoneIT

ASKER

Andrew,

The code works perfectly.

I have one last question on this. How can I make it not ask me to save a copy of the existing workbook. I'd like it to pull the data without being prompted. If you'd prefer I can post this as a second question under Excel.

Regards,
Brian

andrewssd3

Try adding

wbkIndiv.Saved = True

Open in new window

just before the Close on line 102. I was getting that message but I have annoying document management system which I thought was causing it.

RedstoneIT

ASKER

The solution works like a charm. Thank you again for the help !