Hi everyone. I'm hoping to get some help with a macro here. I'm pretty good with formulae, but I've never written a macro, and I can't do this without help.

I'm trying to write some code that will copy the first column (say the first 100 cells) from each of multiple workbooks (names will vary, as will excel versions - xls or xlsx) which are all in a subfolder called "source" and then transpose them row by row into the active workbook. I also want to append the name of the source workbook for each row (after the transpose) at the end of the row so that one can find the source if required.

I know this can be done without actually opening the workbooks, which is key because there are over 300 workbooks in the source folder...

I'd really appreciate the help. This work is for a charity, and I'm just trying to help them out. Would have suggested using Access or something, but have to work with what they've got.