Exporting Data from Multiple Workbooks to a Master List

[bck]

Hi there,

I'm looking for help coding a specific macro multiple workbook task. Here's what I'm trying to do:

I have a folder, titled "Analysis". Inside it there is a "MasterList.xls" workbook and a "Data" folder.
Inside the "Data" folder are all my source workbooks.
The source workbooks are for two variables ('Word' or 'Image'), two conditions each ('6'--which is 'bigger', and '7'--which is 'smaller'), so there are 4 types of workbooks:
"6word_XXXX.xls"
"7word_XXXX.xls"
"6image_XXXX.xls"
"7image_XXXX.xls"
where XXXX is the subject number; there are about 150 subjects, so its 600 files, give or take.
In the each workbook, there is only ONE worksheet per workbook.
In the 'word' workbooks, I need to 'grab' 24 specific data points to export to the MasterList.xls
In the 'image' workbooks, I need to 'grab' 48 specific data points to export to the MasterList.xls
The MasterList.xls workbook has 5 worksheet tabs, only two of which are relevant: "Words" and "Images".

I want the Macro to:

Open MasterList.xls
Make some sort of list of the Data directory
Make a loop using the list of the Data directory
In that loop,
    For each '6word_*.xls" file:
        Open each file
        Grab the subject number (it's always in "A1")
        Grab the 24 specific values
        Export them to the MasterList, "Words" worksheet, on the next empty row in order (so on one row: SubjNum, Value1, Value2, etc.)
    End
    For each '7word_*.xls" file:
        Open each file
        Find that subject's previous line from the other condition (so if the current file is "7word_LZ154", it can take its own subject number from the current file (always "A1") and match it up with the LZ154 line from "6word_LZ154")
        Then, enter the grabbed 24 values from the 7word_*.xls file, starting with column "AA", as thats where all the 7word values start
    End
    For each '6image_*.xls" file:
        Open each file
        Grab the subject number (it's always in "A1")
        Grab the 48 specific values
        Export them to the MasterList, "Images" worksheet, on the next empty row in order (so on one row: SubjNum, Value1, Value2, etc.)
    End
    For each '7image_*.xls" file:
        Open each file
        Find that subject's previous line from the other condition (so if the current file is "7image_LZ154", it can take its own subject number from the current file (always "A1") and match it up with the LZ154 line from "6image_LZ154")
        Then, enter the grabbed 48 values from the 7word_*.xls file, starting with column "AY", as thats where all the 7image values start
    End
End loop

Hope this is clear. Thanks in advance for all the help!

-bck

[royUK]

Here's an example that I posted for someone else recently. It combines data from several workbooks