Extracting data from multiple workbooks into one

[henke_a_]

Hi,
First off, I am a complete newbie at macros. I have found several threads on this topic but can't seem to get it right still...

Background: A survey with 40ish groups (each containing 5 or so persons) have been performed. I want to gather the answers and respondants to all the questions in 1 excel-file.

Problem: I have one folder for each team (named after the team). In each team folder, the structure is the exact same (same amount of folders and filenames). There are several subfolders (8 of which are relevant). Each of these subfolders contains several different folders (one for each question). In each of these folders, there are 2 excel-files that are of interest. One named "meta" and one named "data". "Meta" contains the question, and "data" the answers. The name of the question in "meta" is located in B2. The responses in "data" is located in D2-X (X depending on the number of responses). The respondants are located in "data" H2-X (X depending on the number of responses).

I would like to do the following:
1. Extract the questions from each "meta" file and put them each in row 1, starting from B1.
2. Extract the respondants from each "data" file and put them in column 1, starting from A2.
3. Extract the answers from each "data" file and put them in rows, starting from B2 (the answers need to correspond to the correct respondant and question)

I appreciate all the help I can get. All the best!

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[henke_a_]

I just uploaded an example.

[Glenn Kennedy]

Your sample doesn't make much sense.

You show answer 1 for respondent 1 then answer 2 for respondant 2. Where as answer 1 for respondant 2, answer 2 for repondant 1 etc, etc

Is Q2 on the ext row after Q1.

Please amend to make it UNAMBIGUOUS.

[henke_a_]

Sorry about that! I have made updates to make it more clear.

There are several "Meta" and "Data" files. Each question comes from an individual "Meta" file, and the answers to each question comes in an individual "Data" file.

[Glenn Kennedy]

I have just noticed that this is in the VBA section. So... this will probably be of limited interest to you. However, since I did it, here it is. Formula based.