INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

Hi,

Quote:

However, each file does have a column label of the field I want
Where are the labels?

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
What additional information is needed?

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

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close