Urget Help - in collating data from different excel files

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?

I want to consolidate all the data from different workbook and work sheets into one worksheet.

Hi,

Thanks you so much for your help, but unfortunatly as the file size is too big im not able to upload it. Here i am trying to explain the senerio.

Ok now say I have
Work book 1 contains worksheet 1, worksheet 2, worksheet 3, and worksheet 4
At path C:\Users\myname\Desktop\d drive\folder1\folder2 ( this path can change)

Work book 1
Sheet 1

Have 14 columns of data where the title of the column will be at cell a4, b4,c4 .. and so on
cell a5: n25004 will have some data this may contain blank cells in between

Sheet 2
Cells a2, b2,c2 … will have the titles same as sheet1
Row a3:n25002 will have some data again this can contain some blank cells in between

Sheet 3
Same as sheet 2

Sheet 4
Cell a2,b2 c2 … will have title same as sheet 1
But here the amount of data may not be 25000 it can stop at number like 589 or anything …
So ideally the sheet 1 will have the titles at row number 4 and will have 25k rows of data... then after there can be any number of sheets 2 to 10 or anything … these all will have column titles at row number 2 only.. and will contain 25k rows of data ..but only the last sheet.. say if the workbook has 8 work sheets then the 8th work sheet will have the title at row number 2 but the amount of data need not be necessary 25k .. it can end at any number …

Like this I want a macro the can take data from multiple workbooks and work sheets and put all these data one below the other and create one work sheet only.