Need a VBA to save a file upon closing and name it based upon a cell

[Stryfe]

As always when I have trouble with excel I turn here but not only has this forum helped me with many problems but also taught me a lot as well so thank you for that.

My new problem for the day is been a puzzling for me. I need a VBA where upon closing a file, it will make a copy in another folder and name that file based upon some cells inside the sheet. I created a sample sheet with just the basics needed and in this sheet it says "Series 01313". What I would like to have happen is that when this file is closed or "X" out that it will automatically do a save as function to create a file in M:\TEST and that file be named 01313.xlsm.

Again, thank you for the help and your time.

[Mumps1]

Place this macro in the code module for ThisWorkbook:

Please Login or Register  to view this content.

Please keep in mind that if the location of the numbers changes, the macro will have to be modified.

[Stryfe]

Thank you! That worked great! I'll mark it as solved and I added reputation. For my own understanding could you tell me which part of the code is associated with i3:m3 so if I ever changed it which line I would need to modify? And one more thing and the I promise I won't bug you anymore about this. What if I wanted to change 01313.XLSM to Release01313.XLSM. Where in the code would I insert that so all the saved files would start with "Release" and then the series number? This helps me out more than you will know with understanding the verbiage.

[Mumps1]

This will add "Release" to the file name. The 9 represents the column number for column I and the 13 is the column number for column M. So you would only have to change theses column numbers and the row number which in this case happens to be 3. I hope this makes sense. By the way, you're not bugging me. If you have a question, please feel free to ask.

Please Login or Register  to view this content.

[Stryfe]

Thank you for the bonus work

[Mumps1]

You are very welcome.

[Stryfe]

Mumps1 if I could impose upon you one more time. The code works better perfect for the way you have it but is there a line I can add to it that when someone opens up the file to look at it but does not want to save it, that will prevent it from going into debug mode when selecting "No" when prompted if they want to replace the current copy? I have some employees that are not great with computers (thus the need for the code in the first place)so if the file is visited again and changes do not need to be saved then it will not take it as an error. If not, all good, you helped me out tremendously and I'll just write them down a few instructions to post near the computer.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Stryfe]

Thanks for looking into it. This code brings up the save prompt each time and if I open up an existing copy to view it then will only accept "yes" and it may be we open a file to look at something and put some "what if" numbers in there to see the results but do not want ot save the changes. The previous code worked great so I'll just train them on hitting the "end" button on the debug when it pops up.

Thank you so much Mumps1

[Stryfe]

Mumps1 I am starting a new thread because even if you go thru the bother to help me again I can't give you proper reputation for an additional question.