Dynamic Linking of Workbooks in Multiple Directories

I am trying to link a set of 40 workbooks. The problem is that every month the workbooks are copied and then updated in a new folder corresponding to that month.

Using a macro, I'm trying to build the link using =Concatonate(), and copy and paste it as a value, then add the "=" to retrieve the data from the target book.

Below is the macro I've got so far:

Please Login or Register  to view this content.

I've placed the *** where the problem occurs. The macro directly inputs the value, rather than referencing the cell in general, therefore losing the point of dynamically building the link.

Any suggestions on how to fix this? I'm even open to a different means to the same end.