VBA loop through all workbooks in folder copy data into new summary tab

[Beany213]

I have hashed together pieces of code to pull out a row of data from all worksheets in a folder based on a certain period (this part is manually entered via a message box) next I need to insert a new tab named summary into each workbook and then paste the row of data into it. I am able to do this in part but it is only working when I insert the macro into each workbook, but I need the code to be generic and loop through all closed workbooks in a folder. I have put below my very badly written code which has a lot of duplication but I don't know how to cleanse this without mucking it up and cannot make it work for all closed workbooks, the issue is I think relating to the used.range part but not really sure. Can anyone please help me complete this, I have attached a sample report with tan example of the summary tab required.

Please Login or Register  to view this content.

[AB33]

Beany,
You are working on the same active sheet. You need to loop through a folder using a dir. Ron Deburin website has lots of stuff on merging.
http://www.rondebruin.nl/win/addins/rdbmerge.htm