Need help with Macro - merging multiple excel into multiple excel placed in multiple folder

[ankit1990]

Hi,

Need help with Macro. I have many excels in many subfolder. They are all part of one Parent folder. Each subfolder has two excels. The name of each subfolder starts with same word as the first word of excel that subfolder holds. Each excel has one worksheet only.

I need a macro that runs across each subfolder, merge the excels in that subfolder in a new consolidated excel with two worksheets (one worksheet each for one excel workbook). The macro then closes the consolidated excel and moves to another subfolder. Thus, in the end, each subfolder will have one consolidated excel workbook.

Parent Folder Name "Output"
Sub Folder = A
Excel in Subfolder A = "A Data One 08-25-16.xls" and "A Data Two 08-25-16"
Sub Folder = B
Excel in Subfolder B = "B Data One 08-25-16.xls" and "B Data Two 08-25-16"
Sub Folder = C
Excel in Subfolder C = "C Data One 08-25-16.xls" and "C Data Two 08-25-16"


Output required
Excels in Subfolder A = "A Data One 08-25-16.xls" and "A Data Two 08-25-16" and "A Consolidated 08-25-16"
Excels in Subfolder B = "B Data One 08-25-16.xls" and "B Data Two 08-25-16" and "B Consolidated 08-25-16"
Excels in Subfolder C = "C Data One 08-25-16.xls" and "C Data Two 08-25-16" and "C Consolidated 08-25-16"

Please help.