Summary - Copy the columns based on column headers

I need help to combine multiple workbook(with a single sheet) to a summary sheet using a macro.
I need to combine Book1.xls and Book2.xls to form Summary.xls as attached. There will be multiple books.
All the books will be in the same directory.

The summary.xls sheet will have all the columns from Book1 and Book 2. There will be only four columns all together in Summary sheet
The summary sheet and columns in summary sheet will exist and will not be created by macro.

Need a Macro:
to copy the columns from book1 and book2 based on column header in Summary sheet and paste it in Summary sheet on the corresponding columns.

So the macro will be like
The column header will be stored in a array(No, State, Employee Name, Type)

Step:
will read book1 for No first and if it exist will copy to Summary Column 1 then will read book1 for State and if it exist will copy Summary Column2 and it
will go till the Type. As Type is not found in Book1 that column will be left empty.

Should do the Step for book2.


Thank you.

This below code will do exactly what is required. The summary workbook should have a sheet named sheet1.

Please Login or Register  to view this content.

The above macro solved my requirement. I need help in the above macro

to check the excel file in sub-folders also. It currently checks the excel file only in the current folder.

Thanks