[OBP]

OK, lets do it a step at a time.
I want you to test this code which should load all your Folder names in to Sheet2 and then open them one at a time, at this stage it doesn't actually do anything with them.
You may have to convert this to an xlsx workbook and enable macros etc.

[ccrossing]

It does not need to say where it comes from as it is already labeled in the column (I did not show this on the .xls files I uploaded). So ideally it would look something like this attachment. The order that the files are searched, and thus the order they show up in the spreadsheet is not important.

[OBP]

Have you tested the code in the Workbook I posted, to run the code click on the Sheet2 "Get Files" button?

[ccrossing]

Sorry for the delay--I had to run to a meeting. I ran it once but it hit a Runtime error when it tried to open "~Temperature.xlsx" which is an autobackup file I think.

Running again...I will keep you posted.

[OBP]

OK, if you can get that to work, this version does all the rest.
It opens the files and transfers the data.
What you need to do is ensure that the Worksheet names match the items that you are going to search for ie. dog fish etc.
You also need to ensure that the headings in column A match your actual values.
It works fine with the 2 workbooks that you provided. It is just a case of getting it set up correctly for your actual data.

It runs pretty quckly, so if yours isn't it probably has a problem.

[OBP]

If really necessary you could email some real data to set it up with.

[ccrossing]

It seems to have run well. It ran quickly considering the amount of data it has to churn through. I am going to run the newer version you uploaded and see how it goes.

[ccrossing]

Ok, so I finished making sure my spreadsheets were in the exact format the macro required and...everything worked brilliantly!

Thank you very much for your help and I am deeply indebted to you. This script literally saved me from days of copying and pasting and any accompanying errors.