Copy values to a filel in the same folder

I am trying to take a currently manual process and make it automated through vba. The files are located in the same directory i.e. c:\test\

And for example I wish to copy Column A1:A150 for example to File1.xls to File2.xls B1:B150

I have the vba shown below but have been unsuccessful in making it work for my requirements in this case.

Please Login or Register  to view this content.

Any advise is greatly appreciate as I have over 100 folders in a database in which to reference and it would be brilliant if I would not have to lock the folder location per file. i.e manually adjust the vba per each time. This is a trial for a new system I am in developing so it will eventually span over a thousand folders hence getting this to work what ever the directory is would save me a lot of time.

Thank you