Need help with a macro to pull data from excel file in hyperlinked cell

I'm creating a summary file for a list of projects that I have and am trying to create links between the individual project tracking excel files and the summary file. I have manually entered a hyperlink in a cell in the summary sheet that links to a project's tracking file. I now need a macro to go and open that file and pull data from specific cells within that project's tracking file.

So far I have been able to pull the data for one project, but when I try to run the macro for the next project, it just pulls the data from the first project rather than choosing it from the hyperlinked file.

This is what the macro looks like, without the hyperlink. The summary file is called "Project Register - Adriana Working Copy" and the first file that I am trying to pull data from is called "NIR Project", if that helps. The sheets that I am trying to pull data from in the NIR Project file are "Charter", "Status Summary", and "Benefits", and the cells that I need the data from are in the code below.

Please Login or Register  to view this content.

Thanks!