Consolidating dynamic lists from multiple worksheets to new workbook

**kmsosaka** · 10-04-2010, 09:10 AM

Greetings. I'm an Excel novice with minimal VBA programming experience. This is also my first post, so my apologies if it is difficult to grasp my situation. What I am trying to accomplish is to create a consolidated "master list" in a separate workbook by extracting data lists (and based on specific criteria) from multiple worksheets.

Scenario:
- Multiple individual users will fill out a weekly worksheet entitled "Pipeline" that indicates the status of their assignments (i.e. "1-Open", "1-Retained", "2-Closed", etc.) plus other related information.
- Worksheets all have the same format. For example, column heading in B7 is "Status" and assignment entries will start from B8, B9, etc. where they indicate the status.
- Assignment status will change weekly on each worksheet, as well as the number of assignments. Therefore the number of (row) entries will progressively increase as new assignments are received.
- Each worksheet has cells where they enter their "name" and "team name"

Objectives:
1. Would like to create a macro that extracts and lists only the rows that have the "1-Open" and "1-Retained" status from these individual worksheets into master list in a new workbook.
2. Would like this master list to update dynamically as the individual worksheet data changes
3. Finally, it would it would be perfect if on the new master list it could also include the person's "name" and "team name" next to the respective assignments.

I have attached a sample of the worksheets, and master list that I am trying to compose. I am using Excel 2003.

Again, I am a complete rookie to VBA and any assistance would be most appreciated. I have been searching the forums for similar situations, and although there were some similar cases, I just wasn't able to comprehend how to put this all together.

Cheers! -kmsosaka in Japan

**rylo** · 10-04-2010, 06:50 PM

Hi

This may get you going.

Please Login or Register  to view this content.

It is severely restricted to your example data in terms of sheets actioned etc, and it really isn't automatic. You could change it to a sheet activation macro and attach it to the output sheet so that every time that sheet is actioned, it will update.

Hopefully it will give you some ideas.

rylo

**kmsosaka** · 10-05-2010, 01:08 AM

Thank rylo! This may be a basic procedure for experienced persons like yourself...but to me this information is like a godsend! Your time and effort is extremely appreciated.

I will try out the code today and see how it goes.

Although my attached sample document is just 1 workbook, in reality worksheets will be submitted to me separately by about 50 persons and I will be needing to consolidate the aforementioned data on this separate master list every week.

Again, this may reveal the Excel rookie I am but, how will the code be written differently in such a case? Also, you mentioned that this particular code is not an automated command but if I attached the code to the output worksheet I could make it an automated process. Are you referred to creating some sort of button on this master list worksheet or something else?

Regards,

kmsosaka

**rylo** · 10-05-2010, 06:05 PM

Hi

Various ways to proceed.

1) Open all the workbooks, and run something with them all open - given the number of workbooks, not really practical.
2) Have the code open the workbooks one at a time, process, and then close the workbooks. There are plenty of examples of how to do this on this site, and it really depends on where the workbooks reside, and how they are named. Are the going to be in the same place as the output workbook? Do they have a common naming convention?
3) How many sheets will each data workbook contain? Only 1? If more than one, then how will they be named? And how many of the sheets will be actioned?

Give some more details on the storage arrangements, the naming conventions, and perhaps a couple of example dataworkbooks, as well as the output summary structure.

rylo

**kmsosaka** · 10-06-2010, 10:39 AM

Hi rylo,

Inserted the macro you supplied earlier and it basically does exactly what I'm trying to do....though restricted in function to as you stated. However, it's a great start for me to learn more about coding nonetheless and pretty exciting to know I'm getting close to what I'm trying to accomplish.

Regarding your follow up email, I'll search this site to find some examples of 'codes that can open workbooks one at a time, process, then close' as this is definitely going to be the most practical and easy to manage.

To answer your questions:

1. "Are the (workbooks) going to be in the same place as the output workbook?
Yes, they will all be saved in a folder saved on the desktop called "ActiveOrders"

2. Do they have a common naming convention?
Yes, each workbook file name will be in the format: "PersonName_Report_2010-11"

3. How many sheets will each data workbook contain?
Each workbook will contain 3 worksheets, but I will only be pulling the data from 1 worksheet which is named "Job Pipeline"

As I mentioned, the folder that all these collective workbooks (and the master list) will be saved will rest on my desktop. So the path will be C:\Documents and Settings\Administrator\Desktop\ActiveOrders

If you'll recall, the sample workbook that I attached in my initial post has 4 worksheets (sheets for Persons 1-3, plus the desired master list). So, if you can imagine if these worksheets were separate workbooks and all saved in the same folder...this is basically going to be the storage arrangement that I will have....but not with just the report of 3 persons, but about 50.

So, I guess that if I can figure out how to: (1) utilize the code that you already provided to extract the data from the worksheets; (2) insert the code that will open, extract, and close the individual workbooks that are stored in folder on the desktop, then I hope that will do the trick to create the master list I'm striving for.

Of course, your continued assistance and expertise will be greatly appreciated, especially if you can provide the modified code based on the above information.

Thanks again!

kmsosaka

**kmsosaka** · 10-06-2010, 11:09 AM

Also, I forgot to mention that these workbooks (worksheets) will be sent to me weekly via email (since not all people are on the same network) and I will essentially be saving the attachments in the folder. So as I receive the workbooks I will just be saving and replacing the respective files over the previous weeks.

**rylo** · 10-07-2010, 01:20 AM

Hi

Put the macro below in a general module in the workbook that is going to receive the data. Manually run it and see how it goes.

Please Login or Register  to view this content.

rylo

**kmsosaka** · 10-07-2010, 02:01 AM

Hi rylo.

Put in the code, tried running it and got an "Run time error 9" message on this line

Set OutSH = ThisWorkbook.Sheets("MasterOpen")

Investigating what's going wrong now....

**kmsosaka** · 10-07-2010, 02:13 AM

I think I found where the problem was coming from, and on first glance of running your macro....whoa! I think IT WORKS!!! Will be verifying the data now.

rylo....you are awesome!

You don't realize how much stress and frustration you just relieved. THANK YOU!!!