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

Has each path got the same workbooks that are stored in the array...

It all sounds a bit messy -apologies if I have misunderstood what you want.
I bet you spend hours drilling down through that folder structure
I assume each file name exists only once and therefore exists in only one path
As I am away from my PC, I cannot test any code for a few days but here are some thoughts

On sheet 1 :
create a lookup table of the relevant files and related pathways (Table 1)
- use an Excel "Table" to allow files to be added easily and then sorted
- column1 = file name
- column2 = path

On sheet 2:
The area to select specific files to be consolidated - use another Excel Table (Table 2)
- column 1 select file from dropdown
- column 2 = lookup formula (provides the path from other table)

Now you can refer in your code to Table 2 and that has everything you need

Amend the file names in Table 2 and run your VBA again
Could even have more than one VBA routine using the same "selection area"
Table 1 could be also placed in its own file and be looked up in any code from any file

.... or am I missing something fundamental?