Macro to loop across files, move some records

Attached is a simplified sample file to help clarify my question.

I hope this is not too trivial for the folks in this forum. It's not trivial at all for me, and I sure would appreciate some help.

I have a folder containing many Excel files that are all related to the same task. Each file in the folder will have a consistent name.

For example, this attached file is named, "25_Feb_DummyData.xls". Assume for the sake of this question, that all of the other files in my folder increment the date in the file name, so there would be, "26_Feb_DummyData", etc. There are likely to be over 20 files within the folder.

Each file has a sheet like the sheet in the attached file, again following a consistent naming convention. In the attached sample the worksheet of interest is named, "A1 Records". In the next file it will be something like, "B3 Records", and in another file, "A4 Records".

I need to find this "xx Records" sheet in each of those files.

Next, I need to loop through all of the records and search the "Relevant" column for a value = "Relevant" (versus "Not Relevant").

If the record says "Relevant", then I need to copy that record, and move it to a new file that will contain all of the "Relevant" records from across all of my files in this folder.

My goal is to create a new file with a sheet that contains all of my "Relevant" records from across all of the other files within a folder.

One more thing, the system where I need to do this has Excel 2003.

I hope I am being clear here.

Can someone help me with this?

Sue

Hi

See if this gets you started.

Please Login or Register  to view this content.

rylo

HI
save the attached file inside the folder with data and run the macro from macro sheet. It lists filenames in col A and relavant records from A1 record sheet
Ravi