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.

Sub Macro7()
'
' Macro7 Macro
'

'
    ActiveCell.Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Windows("Project Register - Adriana Working Copy.xlsx").Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Charter'!R3C2"
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Charter'!R4C2"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Charter'!R5C2"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Status Summary'!R6C2"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Status Summary'!R6C4"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Status Summary'!R6C6"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Benefits'!R3C4"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "='[NIR Project.xlsx]Status Summary'!R6C8"
    ActiveCell.Offset(1, -11).Range("A1:L1").Select
    Windows("NIR Project.xlsx").Activate
    Application.WindowState = xlMinimized
    Windows("Project Register - Adriana Working Copy.xlsx").Activate
End Sub

Thanks!