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.

Please Login or Register  to view this content.

I tried below, but I get an error.

Please Login or Register  to view this content.

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?