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

  • Hello. I'm new to VBA and have managed to create a macro that copies my workbook x number of times; however, it fails after the first copy because the filename for the next copy is the same as the first copy. Can you please help me increment the filename? The cell ("G5") that is used to generate the filename contains an integer (e.g., 2345), so I just need to increment it by 1 each time.


    This is what I currently have. Any help you can provide would be greatly appreciated!


    Sub Macro1()


    Dim numtimes As Long
    Dim strSaveName As String
    Dim x As Integer 'Loop counter
    x = Worksheets("Production Record (ATFx)").Range("I2").Value


    For numtimes = 1 To x
    'Get string to save new workbook as
    strSaveName = Worksheets("Production Record (ATFx)").Range("F5").Value & Worksheets("Production Record (ATFx)").Range("G5").Value
    'Copy the workbook
    ActiveWorkbook.Sheets.Copy
    'Save the newly created workbook
    ActiveWorkbook.SaveAs strSaveName
    'Increment the workbook name ?
    Next numtimes


    End Sub

  • Hello and Welcome to the Forum :smile:


    Below is your macro to be tested :



    Hope this will help

    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.
    Thank you very much for your help! I realize there are two things I forgot to mention:


    1) The name of the file should also contain the value in F5, i.e., filename = <F5> <G5>.xlsx, for example, 005 2345.xlsx, where only G5 is incremented. That's why my original macro had the following code:


    strSaveName = Worksheets("Production Record (ATFx)").Range("F5").Value & Worksheets("Production Record (ATFx)").Range("G5").Value


    2) The value in G5 is used elsewhere in the workbook, so I'd like to increment the value in G5 in the copied workbooks -- if the new workbook is 005 2346.xlsx, the value in G5 should be 2346.


    Would you be able to accommodate these requirements? If so, I'd really appreciate it!

  • Hello again,


    Do not know if you have actually tested the proposed macro ... but the New file Name still contains both values in F5 and G5 ...


    A couple of remarks :


    1. If you need to increment the value located in G5, you only need to amend the code as follows :

    Code
    1. Range("G5").Value + i


    2. If you need each G5 cell in each workbook to extract its actual from the file's name ....
    you would have to use a specific formula ... :wink:


    Code
    1. RIGHT(MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1))),4)


    Hope this will help



    Edit : Forgot to mention that proposals are mutually exclusive ... you need to decide which one suits you best ... :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...:)

  • Thanks, Carim.
    I did try the code but, unfortunately, it doesn't work. I pasted the code you provided into my Sub Macro1(). I didn't change the name of the macro because I'm using a button that calls Macro1. When I click the button with the new code, nothing happens.


    FYI, when I click the button using the old code, one workbook is created and then I get a message that the next workbook can't be saved because the workbook already exists.


    Do you have any suggestions?

  • Hello,


    Thanks for your test file ...:wink:


    Attached is the tiny correction ...to generate the files ...


    We still have to sort out the way you want the cell G5 has to behave ...


    Hope this will help


    Question: Are you 100% committed to your rule : all Files Names will end with the 4 digits located in cell G5 ...???

    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, again.
    I downloaded and saved the updated workbook. I'm not sure why, but nothing happens when I click the button. Did you try it on your end? There does not seem to be a Copy command.


    Just saw your question.... Yes, the filename needs to be the serial number, which is "<F5> <G5>".

  • Hi again,


    Attached is an Updated version .. fully tested ... :wink: (like the previous one ... by the way ...)


    The copies are created ... and saved in the very same directory as the Source workbook ...


    Let me know if you are still facing a problem ...

    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...:)

  • !!! Strange ??? :confused:


    Can you use the combination Alt F8 and click Run to execute the macro ???


    Are you using a PC or a Mac ???

    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...:)

  • ALT F8 doesn't work either. I have a Windows laptop.


    Well ... this means that ... depending on your Excel version ... you have to go through the exact procedure ... to enable Macros ... :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...:)

  • Hello,


    Can you access the macros with Alt F11


    Can you copy the two macros ... back to your ' working file ' ...



    And the second macro which goes into 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


    Hope this will help

    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...:)

  • The problem you are facing is quite intriguing ... :yikes:


    I am very eager to know if you have managed to fix it ... !!!

    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...:)

  • You were right about enabling macros. I set the macro setting in Excel to "Enable all macros (not recommended...)" and it generated the files. Great! Since the code is fairly innocuous, I think it should be fine to leave the macros enabled.


    However, I then added the line to increment to value in G5 but it caused a compile error. Please see attached screen shot. Maybe I put the line in the wrong place?

  • Hello,


    Can you go back to the top of this page and look for my previous message # 16 ...


    Try to stick precisely to what is described ... and let me know if you are still facing obstacles ... :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...:)