Excel VBA copying data from closed workbooks with multiple different pathways

Hello,

The below code works for summing column C in the closed workbooks and dividing by 2 and pasting the data into the current workbook with the macro. I am needing to manipulate the formula so it can go into multiple pathways instead of all workbooks being located under the same pathway. Is there a way I can have the folder pathway reference a column (a cell for each pathway, i.e., G1, G2, G3, G4) in the worksheet, so then I can just change the pathway if needed in Excel and then the Macro will reference that for pulling the data?

This code assumes all workbooks are in same folder.

Sub CopyWorkbookData()
    Dim wbNames, i As Long
    Const ws As String = "Trial Balance"
    Const myDir As String = "c:\Users\rhoover\"
    wbNames = Array("Cudahy Center 06.17.xlsx", "Wellington Park 06.17.xlsx", "Wausau 06.17.xlsx", "Forest Plaza 06.17.xlsx")
    For i = 0 To UBound(wbNames)
        Cells(i + 1, 1).Formula = "=sum('" & myDir & "[" & wbNames(i) & "]" & ws & "'!c:c)/2"
    Next

    Range("A1:A4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Range("A1:A4").Select
    Selection.Style = "Comma"
    Range("A1").Select

End Sub

I tried below, but I get an error.

Sub CopyWorkbookData()
    Dim wbNames, i As Long
    Const ws As String = "Trial Balance"
    Const myDir As String = "N:\Team-ASClientAcctgMIN\Team-Accounting\Property Archive\Slate Properties\Properties\GAR\Cudahy\Financial Reporting\2017\ERN001 - 06.17\Reporting\" & "N:\Team-ASClientAcctgMIN\Team-Accounting\Property Archive\Slate Properties\Properties\SUSO 4\Forest Plaza\Financial Reporting\2017\FTM001 - 06.17\Reporting\" & "N:\Team-ASClientAcctgMIN\Team-Accounting\Property Archive\Slate Properties\Properties\SUSO 3\Wausau\Financial Reporting\2017\FLT001 - 06.17\Reporting\" & "N:\Team-ASClientAcctgMIN\Team-Accounting\Property Archive\Slate Properties\Properties\SUSO 4\Wellington Park\Financial Reporting\2017\FSA001 - 06.17\Reporting\"
    
    wbNames = Array("Cudahy Center 06.17.xlsx", "Wellington Park 06.17.xlsx", "Wausau 06.17.xlsx", "Forest Plaza 06.17.xlsx")
    For i = 0 To UBound(wbNames)
        Cells(i + 1, 2).Formula = "=sum('" & myDir & "[" & wbNames(i) & "]" & ws & "'!c:c)/2"
    Next

    Range("A1:A4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Range("A1:A4").Select
    Selection.Style = "Comma"
    Range("A1").Select

End Sub