Thread: Moving Excel Sheets into a folder hierarchy based on keywords in cells A1 and B1.

Let me make sure of your terminology. Do you have a single Excel file that contains multiple worksheets that you want to put in separate files in these directories, or do you have separate excel files with the data in a single worksheet and want the files to be move to the directories? I know what you want to do can be done, but I need to know how your data is setup.

This is a bit complicated and I hope you can figure out what is going on. The attached spreadsheet contains a macro. You will need to read the comments in the macro to see what is happening and what you need to do. There are two sheets - one for keywords and one for file paths. Also notice the button on the Keywords sheet that makes the macro run.
MoveFiles.xls

Originally Posted by bader

Thank you so much for this. I tried it out and I have a few questions. I would really appreciate it if you would be kind enough to answer them.

1) I tried moving an excel file but I keep getting an error saying that the file (which is apparently the first file in the input folder) does not exist. Does the excel file have to be read only? And what if the text in A1 and B1 is formatted i.e. (Bold, underlined)

Did you check the path to the input files. It is on the Paths sheet in the original book. The path must have "\" rather than "/" between the subdirectories. The file does not have to be read only. I have opened it read only to prevent updates from occurring during the process. Also, the format of the text on A1 and B1 has no impact on the process. In fact, capitalization has not effect either as the Ucase function is used to do the comparison in upper case.

Originally Posted by bader

2) Do the keywords have to be the exact folder names in the hierarchy? For example, if I had a list of keywords like this "Finance", "Department", "1999", does the corresponding output folder need to have a directory hierarchy with the exact names Finance> Department> 1999?

The way that it is written now requires the subdirectories to appear in the same order as the key words. That can change if desired.

Originally Posted by bader

3) Would a solution similar to the attachment be possible? In the attachment the idea is if a combination of keywords exist then move file into this specific directory. All keywords mentioned must either exist in Cells A1 and B1 in order for the move to happen.

That is an excellent solution to the problem of 2)! Please note that the file will be moved to first directory where all the keyword match, so make sure the list of keywords in unique.

One feature of the original macro is that the first time an excel file in the input directory does not conatin a keyword match, the macro will stop. This is due to a feature of the Dir function. If you need this to continue if a match cannot be found, let me know and we can adjust the macro.

Let me know how you want to proceed.