Macro to copy workbook multiple times and name copies based on incremented cell value

  • You are heading ... in the wrong direction ... :wink:


    The main macro you need ( to be stored in the module called Module1 ) is the following :



    AND


    To be 100 % sure your sequence number gets incremented properly ...


    you do need a second macro to be stored in the module called ThisWorkbook ...


    Code
    1. Private Sub Workbook_Open()
    2. Sheet1.Range("G5").FormulaR1C1 = _
    3. "=RIGHT(MID(CELL(""filename"",R[-4]C[-7]),SEARCH(""["",CELL(""filename"",R[-4]C[-7]))+1,SEARCH(""."",CELL(""filename"",R[-4]C[-7]))-1-SEARCH(""["",CELL(""filename"",R[-4]C[-7]))),4)"
    4. End Sub



    It will solve all your difficulties ... :wink:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • It works like a charm! Thank you so much for all your help. You are fantastic!


    How can I put it ... A HUGE RELIEF ...!!!


    Glad to hear you have managed to fix your problem ... :smile:


    Thanks for your Thanks ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi, Carim.
    I just realized that the value in G5 is a formula. I'm not sure that will work for how I want to use the template. I want the owner to be able to enter the starting serial number in G5 manually.


    The process is as follows:
    [INDENT]1. Open the template, which will be named something like, "ATF5 template.xlsm" (no serial number in the name).


    2. Enter the last-used serial number in G5 manually (e.g., 005 3782).


    3. Click the button to generate the specified number of files (e.g., 3) in sequence (e.g., 005 3783, 005 3784, 005 3785), and update the value of G5 in the generated files (not in the template).[/INDENT]


    Is there another way for you to update the value of G5 in the generated files?

  • Hello Nancy,


    You should not worry too much ... :wink:


    Despite the fact that there is a formula in cell G5, your user can always type the sequence number he wants ... on top of the formula ...


    However, if you feel your user could be reluctant to type a number on top of the formula ... just let me know ...


    :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Oh, I see. That's great. I've done some more thorough testing and it seems to be working really well. Thanks, again, for all your help!


    Great ... !!! :smile:


    Forgot to ask you : would your user appreciate getting, at the very end, a message letting him know his request has been correctly completed ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks, I've already added a message "Workbooks generated." that displays at the end. I also added a second button called Process Complete that the user can click to password protect the generated workbook (e.g., 005 1234.xlsm). So after the user enters all the required information in the workbook, they click Process Complete, which applies a password to the file. After the file has been closed, you will need to enter the password to open it.


    I tested these new items and everything worked great. But after moving the template to another folder, it stopped working. Even when I go back to the old file in the original folder, it's no longer working. The behaviour seems very unstable to me. I should mention that I also password protected the template so you have to enter a password to open the file.


    I've attached the latest file. This is the process that I follow:


    1. Open the template (ATF 5 Template.xlsm).
    2. Type the password "unlock", and press ENTER.
    3. Type a new S/N in G5.
    4. Type the number of workbooks required in I2.
    5. Save the workbook.
    6. Click Generate Workbooks. The message "Workbooks generated." is displayed.
    7. Check the folder where the template resides. No generated files!


    Finally (I know it's a lot to ask!) but I wondered if it's possible to create a folder and place each generated file in its own folder? The folder should have the same name as the file appended with "sensor" (e.g., 005 1234 sensor)? This is what the end-user would like. I thought maybe we could add a path to the saveas filename? Will the path be created if it doesn't already exist?


    I hope you can help!

  • Hello again,


    Attached is your revised Template ...


    As requested, the generated files are now stored in the sub-folder correctly named ... :wink:


    Hope this will help


    P.S. Congratulations on your very quick progress ...:smile:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi, Carim.
    How are you? I have a follow-up question on this. Everything is working great, except the button that password protects the completed file.


    This is the process:
    1. Open the template (file is not password protected, just some cells on two of the worksheets).
    2. Click the button to generate required # of workbooks in their own folders.
    3. Open one of the generated workbooks.
    4. Complete the required information on the worksheets.
    5. Click the button to password protect the workbook, and then close the file. THIS DOES NOT WORK. When you open the protected file, it does not ask for the password.


    I tried the code on a blank workbook and it worked fine. Can you please take a look at the attached workbook again?


    Nancy

  • Hello, Carim.
    Can you please check the file you sent? The Generate Workbooks button is not working in my downloaded version, so I'm not able to test any further. Please see the compile error attached.


    Also, I did not password protect the template because that would cause the generated copies to be password protected, isn't that right? The copies need to be unprotected so that others can open the workbooks (without a password) and enter the information. It's not until they're finished completing the information that I want them to click the button to lock it to prevent any further editing.

  • Hello,


    When it comes to protection ...


    You should carefully differentiate the protection at the worksheet level ... and the protection at the workbook level ...


    My personal recommendation would be ... to completely forget this issue of Protection ... ( which, by way, within Excel, can be very easily breached ...)


    After all, for sure, you want people to work .. and not build unnecessary hurdles which might deter them from using your tool ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I'm quite clear about the difference between protection at the worksheet level and protection at the workbook level. Unfortunately, these are the requirements. But that's OK, I'll figure it out.


    Thanks, again, for your help!