Collate data from Multiple Excel Files

Hi,

Am amateur at best in VBA. I need a Macro which will collate value of cells (not formulas) from specific cells in each excel files in a folder, and then collate them in a separate excel file.

I have put together a code from bits and pieces from my earlier practice projects, however, as expected, it was not working. So after a lot of head scratching I modified the code to quite an extent and almost got it to work!

However, I am getting only the value of cell B5 from each file in the folder in all the 7 columns of BaseWks...

Am pretty much sure that my mistake is in the Range syntax or something like it...

Please Help!!!

Please Login or Register  to view this content.

Sample form attached from where the data needs to be collated.

Hundreds of such files are in a folder which the macro needs to process.