VBA Macro to Pull Values From Excel Files into Summary Excel File

[EBox]

Hello:

I have about 50 files in a certain folder where I would like to pull 4 values from each spreadsheet and assemble in a new "master" spreadsheet.

Unfortunately, each file is not entirely the same layout structure, so I cannot simply pull all values from the files that are in discrete cells like "pull values from A2, D4, E13 and F21". However, each file does have a column label of the field I want, which has the value I need to pull located 5 columns to the right of the label. (For example, File 1 has the label in D5, so I want to pull the value from D10. File 2 has the label in D4, so I want to pull the value from D9, etc.). This is the case with all 4 metrics.

I am a real newbie with VBA/macros and would appreciate it if someone can help with a macro for Excel that can basically run through all Excel files in the folder and pull the values I need into a spreadsheet that summarizes the data into the attached format/layout.

I'd be so grateful for the help!

Thanks in advance!

EBox

 

[SkipVought]

Hi,

However, each file does have a column label of the field I want

Where are the labels?

located 5 columns to the right of the label. (For example, File 1 has the label in D5, so I want to pull the value from D10. File 2 has the label in D4, so I want to pull the value from D9, etc.)

Do you mean 5 ROWS below the label?

Your uploaded workbook contains no VBA code at all? What have you tried?

Please answer all these question with as much detail as possible.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EBox]

I haven't tried any code because I'm very new to VBA and this is way beyond my abilities.

The row labels are:
"Net Revenue"
"Total expenses"
"EBITDA %"
"Visits"
"RVU"

Note that sometimes these row labels will be in different rows (e.g. "Net Revenue" may be on row 200 in one file but on row 252 on another). And sometimes they will be in different columns (e.g. one file may have them in Column A, another may have them in Column D), but they will all always be in the same column (e.g. never will "Net Revenue" be in Column D while the others are in Column E).

To clarify, depending on the month, we would want to pull from a specific column for each of the rows that have the aforementioned labels. Each column reports out a particular month (e.g. Jan, Feb, etc.), and below the month is either "Actual" or "Forecast Interim". We would want to pull from the rightmost column that has an "Actual" in it (indicating it is the most currently processed month) as well as the column labeled "Total" which has the YTD.

I hope this helps and thanks so much.

 

[SkipVought]

Well since you are a programmer you know the detail of a requirement.

So we need such detail, especially since you've thrown another curve of varying columns for date.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EBox]

What additional information is needed?

 

[SkipVought]

depending on the month"

What month? In your Sheet1 (a rather generic, nondescript name) there is no indication of data related to date.

"I have about 50 files in a certain folder"

What folder? Are there only Excel files for this project in this folder?

"Unfortunately, each file is not entirely the same layout structure"

Please post at least one example.


In your sample Book1.xlsx (what, no workbook name?) Value 1, Value 2, Value 3, Value 4
Really? Not Net Revenue, Total expenses, EBITDA %, Visits, RVU, which is FIVE values, not FOUR?

So here's what you need to do in addition to providing this basic information. I need a functional requirement, describing how this process should work. Naturally you'll need to use the additional information, that you failed to provide initially, in the description that you will provide. This is what any programmer would need to be able to code a solution.

Oh, yes, of course you'll need to modify and upload your target workbook, as it is deficient of essential information.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]