Sorting excel workbook files into seperate folders based on what is in a certain cell

I have a folder that contains over 5,500 excel Workbooks. Each workbook has about 5 worksheets and the workbook files are named 123456789.xlsx or some variation of a customer number. I need to sort out the ones that in sheet one, cell d4 read CPA. I really do not want to have to open every single one just to look at one cell and move it to a seperate folder. Any help would be appreciated.

Bump no response

Perhaps a macro like this?

Please Login or Register  to view this content.

You will need to change

Please Login or Register  to view this content.

as well as

Please Login or Register  to view this content.

to the appropriate folders.

Based on your information this macro looks for Sheet1 and cell C4. If no sheet1 found it will show all sheets and you have to select one or the macro will fall flat on its feet. The way its set up it will find files ending in .xls, .xlsx and .xlsm

When moving files from source folder to target folder if conditions are met i.e. D4 = "CPA" this macro will not move the file if there is a file with the same name in the target folder.

Even if I have tested this macro I would strongly suggest you make a copy of the folder containing your data files!

When run the macro first loops through all files in "Mydir" and copies all file names and the D4 value from sheet1 to the workbook (sheet1) where you install this macro.

It then sets up an auto filter on sheet1 filters so only files with "CPA" in D4 are shown.

The result is copied to sheet2 and macro then loops through all file names on sheet2 at the same time moving the files.

Alf