Extract selective data from multiple worksheets into a new workbook

Using Excel 2010.

Hi forum. I've been working on some macros lately but I'm definitely no VBA expert. So I'd like to seek some help here.

I have multiple workbooks in the same folder with 2 worksheets in each:

- Summary

	A	B	C	D	E
1	ID	Dealer	Location	Course Name	Course Type
2	CIV001	Civic	Manila	Warranty	GEN
3	CIV002	Civic	Manila	Basic Mechanics	TECH
4	CIV003	Civic	Manila	Basic Computer	IT
5	CIV004	Civic	Manila	Advanced Mechanics	TECH
6	CIV005	Civic	Manila	Warranty	GEN

- Participants Name

	A	B
1	ID	Name
2	CIV001	Tom
3	CIV001	Henry
4	CIV002	Carol
5	CIV002	Michelle
6	CIV002	Tom
7	CIV003	Alan
8	CIV003	Ben
9	CIV004	Charlie
10	CIV004	Peggy
11	CIV004	Alan
12	CIV005	Henry
13	CIV005	Michelle
14	CIV005	Ben

Let's say, I wanna create a macro that will extract IDs CIV002 to CIV004 from both Summary and Participants Name from all the workbooks into a new workbook with 2 similar worksheets. How do I come about to do it?

Thanks a lot!

Hello and welcome.

Try this macro. This is assuming all your IDs are beginning with "CIV" and has 3 digits number. It will prompt for a starting number, an ending number and for the workbooks. It is also assuming all your worksheets in the workbooks are named the same.

Please Login or Register  to view this content.

Try this amended. There's a new prompt now asking for the dealer's ID, and should now copy any number of columns. You did not post any sample workbook so it's difficult to visualize what went wrong for number 3. Did any error occur for that?

Please Login or Register  to view this content.

Oh it was my error, managed to figure it out. Apologies for that one.

Yeap this works pretty well for now! Thanks a lot!

Alright, hahaha sorry to trouble again :/

Let's say I've filtered the data I need in the Summary worksheet. Is it possible for write a macro that will do a lookup and copy the participants' name according to whatever IDs copied from the SUmmary worksheet?

This is the current code I'm running which allows me to extract all the data from the Summary worksheets in all the workbooks, after I have filtered the data I need.

Please Login or Register  to view this content.

I am confused now. So you want participant's names to be in the same worksheet as Summary? Can you post a sample workbook (with sensitive information removed) showing how the desired results look like?

Sorry for the confusion. I've attached a sample of the end results I'm looking to get, note it's an example with only 2 dealers.

I see that there's an additional column named "Company" now in Participants Name worksheet, is that taken from Summary worksheet? If so, which column is that?

But you mentioned you want to copy participants' names.

So which column/data should be copied, and from where to where?

Ohh I have missed that out in my first post, sorry about that. It's rightfully in the Participants Name worksheet.

I wanna create a compiled workbook, with all the data in the "Summary" worksheet from the different workbooks, and all the data in the "Participants Name" worksheet corresponding to the IDs in the "Summary" worksheet copied.

Which means I should have one big new workbook, which consists of "Summary" And "Participants Name" worksheets with all the data complied.

Originally Posted by bukit13

Which means I should have one big new workbook, which consists of "Summary" And "Participants Name" worksheets with all the data complied.

Right... it sounds just like what the code I've posted should be doing, is it not doing what it should be? Just in case you haven't realise, you can select more than one workbook when prompted by the macro, just Control+Click the file names.

Originally Posted by millz

Right... it sounds just like what the code I've posted should be doing, is it not doing what it should be? Just in case you haven't realise, you can select more than one workbook when prompted by the macro, just Control+Click the file names.

Hmmm alright. I'll try to figure it out. Thanks a lot anyway, really appreciate the help!

No problem, but if the code is not working as intended maybe you need to explain in more detail what needs to be done and what not.

Otherwise, if everything is fine, please mark the thread as [SOLVED].