Collating data into single worksheet

[akshay1234]

I have two workbooks with 3 tabs each.

I want to collate data from above 2 workbooks into a single worksheet.The macro should copy header from only one sheet and rest worksheet data below it.

The data count in each tab is not fixed whenever I am going to run the macro.

Any help on how I can build a macro for this would be greatly appreciated.

Thanks for your help.

 

[oldbrewer]

on each tab in a helper cell use a formula to count the number of rows of data (sumproduct((a1:a10000<>"")*1)

then your macro will know how many rows to pick up

in your first workbook pull over data from workbook2 by using in A1 =[workbook2]sheet1!a1
obviously a different sheet for each sheet in workbook2

now the macro is very simple