Macro to save multiple workbook based on Dropdown

  • Hi,


    i have a drop-down list (Cell C5) in Tab name Worksheet.
    The drop down list contain a list of ID
    is there any code that can make excel auto run through the drop down list, select each of the ID and auto save it as a new file in a specific folder (file name i have a specific name etc: worksheet_(the ID number selected in the drop down list)?
    I am very very new to macro and only know how to use record.
    Please help and thank you in advance!

  • Please attach a copy of your file. Give a few examples of what you want the new file names to look like based on the ID's in the drop down. What is the full path to the folder where you want to save the new files? Do you want to do this with ever ID in the drop down list or just one ID at a time as you select it?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps!


    Thank you so much!
    Attached file name sample..i insert a button (generate) in project list tab beside the button refresh. which i wish that once i press generate it will go through the drop down list in workload tab cell C5 and save each file in c drive document. Each new save file contain the same tab as this sample file, just that the C5 cell has already been chose. file name to be as worksheet_AAA11109, which worksheet- is standard, behind AAA11109 is based on their ID in C5


    please see sample worksheet-AAA11109.
    * the reason behind is because each ID has been assigned different project.


    Hope i did not confuse you

  • Do you want to create a new file only for the one ID that is displayed in cell C5 of the "workload" sheet? What is the name of the folder in the C:\ Drive where you want to save the new files?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • HI Mumps!


    i want it to loop the drop down list until the last ID name. and i want to save it at my document folder, folder name working file.


    Thank you so much!

  • Since the drop down list refers to B3:B22 in sheet "ID", the macro uses that range to access the ID's. Make sure that you have a subfolder named "working file" within the "Documents" folder before running the macro. This macro will create 20 new files.


    Code
    Sub Generate()
        Application.ScreenUpdating = False
        Dim LastRow As Long, ID As Range, srcWS As Worksheet
        Set srcWS = Sheets("ID")
        LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each ID In srcWS.Range("B3:B" & LastRow)
            ActiveWorkbook.SaveCopyAs Filename:=Environ("userprofile") & "\Documents\working file\" & "worksheet-" & ID & ".xlsm"
        Next ID
        Application.ScreenUpdating = True
    End Sub

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps!


    OMG you are amazing!! the code works.Thank you so much.
    Can i know is it possible for it create the new files at the same time it will auto choose ID no in workload tab?
    means the file it created worksheet-AAA_11109, the workload tab ID cell c5 will show AAA)11109?


    Thank you so much!!

  • If I understood correctly, this should work. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name "workload" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in C5.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target.Range("C5")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        ActiveWorkbook.SaveCopyAs Filename:=Environ("userprofile") & "\Documents\working file\" & "worksheet-" & Target.Value & ".xlsm"
        Application.ScreenUpdating = True
    End Sub

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    May i know do i still need to assign the generate code? im not sure if my understanding is correct. i tried the first code you gave me and it works by creating the new files. so i insert the code you said in workload tab..i selected the first ID in workload tab and click generate, it create the files but all the files have the same ID in workload tab... if i remove the generate code "the first code you provided" i have an error saying "compile error: Argument not optional" and it highlight Intersect.

  • Hello,


    A tiny tiny mistake ( with a period instead of comma ) very very unusual from Mumps ...:wink:
    Another simpler approach ... which avoids Intersect ...


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Address <>""$C$5" Then Exit Sub
       Application.ScreenUpdating = False
       ActiveWorkbook.SaveCopyAs Filename:=Environ("userprofile") & "\Documents\working file\" & "worksheet-" & Target.Value & ".xlsm"
       Application.ScreenUpdating = True
    End Sub


    Hope this will help

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

  • [USER="31712"]Carim[/USER]
    Thanks for picking up on that. I have to be a little bit more careful. :)
    [USER="333599"]woodwood[/USER]
    You don't need to click the generate button. As soon as you make a selection in C5, the macro will run automatically and create a new file for the selected ID only. You just have to make sure that you placed the macro in the worksheet code module as I described. You can use Carim's suggestion or this revised version of my suggestion:

    Code
    [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target,Range("C5")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        ActiveWorkbook.SaveCopyAs Filename:=Environ("userprofile") & "\Documents\working file\" & "worksheet-" & Target.Value & ".xlsm"
        Application.ScreenUpdating = True
    End Sub[/SIZE][/FONT][/COLOR][/align]

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • oh! thank you so much [USER="31712"]Carim[/USER] and [USER="138669"]Mumps[/USER]! is there anyway it can be auto that i do not need to click each ID? because i want to generate all ID everytime.


    thank you so much!! both of you are awesome!!

  • woodwood
    Maybe just for clarification: If you make a selection in C5, the workbook for that ID only will be created. If you want to generate workbooks for all the ID's, just click the "Generate" button.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • [USER="138669"]Mumps[/USER], does it mean i cant have both... i want it generate workbooks for all the ID with the ID selected in workload tab.
    Thank you so much for explaining!

  • Replace the previous WorkSheet_Change macro with this one:

    Now workbooks for all the ID's will be created when you make a selection in C5 and when you click the Generate button.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • now selection in C5 will create all the IDs but all the ID is the same..
    sorry that i did not make my question clear.
    I hope it will be once click generate or select the drop down list, all ID will be created and the ID in workload tab C5 will be the same as the file name.
    Meaning for file AAA_11109, the workload tab c5 is AAA_11109. file AAA_11121, the workload tab c5 is AAA_11121.
    the new code worksheet_change macro works fine but the cell C5 is not the same as file name :(


    Is that possible?

  • I still don't understand. The file name will look like this: "worksheet-

    AAA_11109". Do you want

    [SIZE=13px]"worksheet-[/SIZE]

    AAA_11109"

    to appear in C5 instead of "AAA_11109"?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Does this do what you want?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Replace the "Generate" macro with this one:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!