Excel VBA copying data from closed workbooks with multiple different pathways

**Hoover5896** · 07-10-2017, 12:50 PM

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.

**Sintek** · 07-11-2017, 04:44 AM

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

**Hoover5896** · 07-11-2017, 04:20 PM

No each pathway only has one workbook. So I am guessing I need to setup some other variable instead of an array to go into each folder and pull the workbook data, but I am not sure how to do that?

End result, I am trying to create something that can go into up to 75 different folder pathways and pull different workbook names, i.e., Cudahy Center 06.17.xlsx", "Wellington Park 06.17.xlsx", "Wausau 06.17.xlsx", "Forest Plaza 06.17.xlsx all that have "Trial Balance" as the sheet name and sum column C and divide by 2, then insert that number into column A1, A2, A3, etc. of current workbook with macro.

**kev_** · 07-11-2017, 05:32 PM

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?

**jindon** · 07-11-2017, 09:02 PM

Originally Posted by Hoover5896

Hello,
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

Please Login or Register  to view this content.

**Hoover5896** · 07-12-2017, 09:54 AM

That worked perfectly! Making it even better, can I make it so the workbook names can be referenced in column F too, so I do not need to go into VBA and type each workbook name anytime there is an update and can just do so in column F?

Please Login or Register  to view this content.

**jindon** · 07-14-2017, 06:23 AM

Do you mean

Please Login or Register  to view this content.

**Hoover5896** · 07-14-2017, 02:58 PM

It looks like I get a "Compile Error - Expected Array" with this. I am trying to do the same as what we did in G2, G3, G4, etc for following the folder pathway, but having the folder pathway typed in Excel and not the VBA so it is easier to update, but instead doing it in F2, F3, F4, etc. to know the workbook name it needs to go into instead of listing all the workbook names out in VBA.

**jindon** · 07-14-2017, 09:38 PM

Ah,

Please Login or Register  to view this content.

It should be the number that you have value in col.F.
Perhaps

Please Login or Register  to view this content.

Excel VBA copying data from closed workbooks with multiple different pathways

LinkBack

Thread Tools

Rate This Thread

Display

Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Re: Excel VBA copying data from closed workbooks with multiple different pathways

Thread Information

Users Browsing this Thread

Similar Threads

Copying specific cells from different closed workbooks to a Master Excel file

Copying data from a master worksheet to a folder full of closed workbooks

Copying specific data ranges from multiple (closed or in use by others) workbooks

Copying rows from multiple closed workbooks to active workbook

[SOLVED] Copying cells from multiple closed workbooks to active workbook

VBA: Copying data from closed workbooks

Copying data from closed Workbooks

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions