Move open workbook to different folder based on the value of a certain cell

[mikkelsl]

I feel like, I have tried everything to make it work with the functions: Name / FileCopy / Kill
I can't make it work..

I have a bunch of excel files. I want to make a macro in each of them, where you can change the status of the workbook from f.i. "Active case" to "Order".
Based on the change in status, the file should be moved to a different folder.

If you can't base a macro on the change of a cell value, it's fine with a button as well - the problem is, how do I move it with a macro?

I think the reason, why I haven't been able to make it work, is because the macro is in the open workbook that is supposed to be moved.

Does anyone know of a solution to this problem?

[buran]

use worksheet_change event to run macro when cell value change.
1. get the full name i.e. path & name of the open workbook in a variable
2. save as the workbook to new location. now the open workbook is the one at the new location
3. delete (kill) the old file (full name already stored in variable)

[buran]

deleted double post

[LJMetzger]

I think the reason, why I haven't been able to make it work, is because the macro is in the open workbook that is supposed to be moved.

That is correct. When a file is open in Microsoft Office, the file cannot be deleted or moved.

I want to make a macro in each of them, where you can change the status of the workbook from f.i. "Active case" to "Order". Based on the change in status, the file should be moved to a different folder.

You can solve your problem the way buran suggested, which is a sound method that will work effectively.

Alternatively, you can change your software design to run your macros from a Master Workbook that has no data. It can change the status in your data files, open and close the data files, and move the files to the proper folder.

Lewis

[mikkelsl]

Hi buran and LJMetzger, thanks for your answers.

That's exactly what I've been trying to do actually, but I can't make it work. If you can see, where I'm mistaken, please let me know.

Please Login or Register  to view this content.