Need help in collating data from multiple excel files

[Shruthi]

I have a set of workbooks (each workbook containing one or more worksheets) located as some folder in local drive.
Now say
Work book 1 contains worksheet 1, worksheet 2, worksheet 3, and worksheet 4
Work book 2 contains worksheet 1, worksheet 2
Work book 3 contains worksheet 1, worksheet 2, worksheet 3, worksheet 4, worksheet 5, worksheet 6
And so on ….
Now in each work book say work book 1 I have worksheet1 that has title at row a4 and has 25000 rows of data, and then there is worksheet 2 that has title at row a2 and then 25000 rows of data then worksheet 3 is same as worksheet 2 with title at a2 and 25000 rows of data and work sheet 4 has title at a2 and may be some 9352 rows of data
So ideally in each work book at work sheet 1 the data title will be at row a4 and then there will be 25000 rows of data and from worksheet 2 to some number of worksheet there will be title at row a2 proceeded with 25000 rows of data in each sheet. And finally the last sheet of the same work book may or may not have 25000 rows of data; it may end up at some abrupt number like 582 or 9681 or anything.
I have a requirement where I want to collate the data present in each workbook (workbook 1,workbook2, and so on … ) into one master sheet. Can anyone please help me with a macro?

[parttime_guy]

Hi Shruthi,

Try this post

http://vbaexpress.com/kb/getarticle.php?kb_id=773

Create a Master Folder on ur C Drive
Copy all files to be merged/combined in the Sub-directory
Run Macro
Watch the magic

Hope it solves ur problem.

Happy Excelling

[Shruthi]

Hi,

Thanks i had seen this post but it does not solve my problem. my problem is similar to this but not same and hence it does not help much.

[parttime_guy]

Hi Shruthi,

I have been using the above tool quite often - and it has simplified my work.

Is it possible to attached a file/worksheets with about 10-20 rows with sample data along with the results u are trying to achieve - maybe will try to come up with a solution.

Regards