Copying 1 Column to Master Workbook from multiple Workbooks

Hi all,

Wondering if I can pick your brains. I have a training log that I am trying to automate. Each staff member has a Workbook (all in the same folder but with their name) with names of training, code, refresh rate, type and date completed in Columns A-E respectively. What I am trying to do (and struggling with) is the copy all of this data into the Masterbook which the management will use to generate reports.

Key points:
Staff workbook - worksheet of interest is called StaffTrainingLog.
Column within StaffTrainingLog that I want to copy is Column E which has name of the staff member in row 1 followed by training completion dates from row 2-200 (this is all in Column E only and no other columns interest me).
Important note regarding the above is that the staff may not have completed all training so therefore there is a chance that there will be blanks within that range so it is important that all of range E1:E200 is copied.
This is then to be copied to the MasterWorkBook called MgtMT.
The worksheet within MgtMT this data is to be copied to is called MTStaff.
Data is to be copied to Column B1:B200 for the first StaffTrainingLog and in the same order (the training will already be prepopulated in Column A).
This is to be looped for all other xlsx files within the folder and the following data to be pasted onto columns B, C, D etc so that it Offsets a column for each StaffTrainingLog.
I guess it would also be useful to have a delete range B1:BZ200 for MTStaff at the start of the macro so that each time they hit the macro the latest data is presented to them.

I hope that makes sense - I couldnt upload my actual file as my work computer doesn't let me use this website and that the data might be considered sensitive even though I have anonymised everything.

I was trying to do this myself by recording macro's and mutilating other peoples VBA code but I seem to have killed it!

Any and all help is greatly appreciated.

Untested.

Please Login or Register  to view this content.

Thanks to both of you.

Alphafrog - unfortunately not allowed any addons for any software at work - thanks for the links, i have tried to construct my own mixed breed of a code using that prior to coming to here and as i struggled with the offset of a column I kind of had an adult tantrum and hit the delete button on numerous files so start again and then just be completely lost!
Jindon - the code allows me to select a folder at the dialog box but once i select the folder it does not progress from there.

thanks again

Jindon - ignore my comment above as I just needed to change the filetype from xls to xlsx and it seems to have done the trick - thanks very much for that.

I will try and adapt it a bit and if (and only if) I hit a brick wall again I will most likely come back to pick your brains

thanks again.

If you have trouble with the extension, just change it to

Please Login or Register  to view this content.

You are on xl2013 according to your profile, so Dir with *.xls" should read xls, xlsx, xlsm etc... all those extensions. though.