Macro- save mult. wrkbks based on dropdown

Microsoft Excel 2003 (full product)
January 20, 2010 at 10:12:51
Specs: Windows XP
Hi there,

I have one spreadsheet with a drop down of roughly 200 names (dropdown in cell H5), each selection then populates other data based on the dropdown. I need to create an individual workbook for each of the items in the dropdown.
H5(List of names in drop down)

I would open my master workbook, select "bob," then 'Save as' "Bob." Move on to next name. select "Sue," 'Save as' "Sue", move on... Is there a macro that can do this for me?
If so, can I have the file name be 2 cells put together? Each name has a number that is attached (like an employee number), when that persons name is selected the number automatically pops up in cell H7 using a VLookup. Can the Macro save each file name based on the data located in 2 cells? IE-
H5- Sue
H7- 11
File Name: 11-Sue


See More: Macro- save mult. wrkbks based on dropdown

January 20, 2010 at 11:47:00
Right-Click the sheet tab for the sheet with the drop down, choose View Code and paste this code in:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$H$5" Then
  fname = Range("H7") & "-" & Range("H5")
  ActiveWorkbook.SaveAs Filename:=fname
 End If
End Sub

Whenever H5 changes, the variable fname will be set to the values in H7 and H5 with a dash in between, as in your example.

The file will then be saved using fname as the file name.

As written, Excel will ask you if you want to overwrite the file if that filename already exists, but that can be dealt with if need be.

Let me know if this works for you.

Report •

January 20, 2010 at 11:56:05

It might be easier if you use a macro to populate H5 with each name from the dropdown list instead of manually using the drop down.

For example, with the list of names in A1:A3, this will put each name in H5, let the VLOOKUP do its work and then save the file with the current values from H7 and H5.

Option Explicit
Sub MakeEmpFiles()
Dim nxtName, fname
 For nxtName = 1 To 3
  Range("H5") = Range("A" & nxtName)
   fname = Range("H7") & "-" & Range("H5")
   ActiveWorkbook.SaveAs Filename:=fname
End Sub

Report •

January 20, 2010 at 12:00:20
It works! Thank you kindly :)

I just need this to happen once though.... Basically, I have the one spreadsheet that I need to turn into 200 spreadsheets but I don't want that code to be there on the new saved files.

When I open one of the "saved as" files, I need that code erased so it doesn't keep saving new copies everytime I change the drop down. Do I have to go in and erase the code in each of the 200 sheets?

Report •

Related Solutions

January 20, 2010 at 13:38:27
My second suggestion (Response # 2) eliminates that problem because it doesn't use the Change event.

If you run that code once, it will create the 200 woorkbooks without the need to manually use the drop down 200 times.

Yes, the code will be stored with each workbook, but as long as you don't run it, it will sit there doing nothing.

BTW...I am a little curious...

Why are you creating 200 sheets, one for each employee, and then still using the dropdown?

Report •

January 20, 2010 at 13:48:56
I thought about that and I think you are right, if I have one for each employee I won't need the drop down feature anymore.

That being said, I don't think I am quite understanding how to get the data into H5 without a dropdown... The dropdown is currently running through a list on the next worksheet (=FacNum!$A$2:$A$102). Sorry for being daft, but I am new to the Macro thing and don't know exactly what to do.

By the way, I really appreciate all your help so far, thanks!

Report •

January 20, 2010 at 19:05:17
You're not being daft, just ignorant - which is OK.

Ignorant just means you're "lacking in knowledge or training", it doesn't mean you're stupid or daft.

Anyway, try this code. It will loop through the cells in the list you have in FacNum!$A$2:$A$102 and populate H5 with each item 1 by 1, saving the file each time.

As I said before, "Yes, the code will be stored with each workbook, but as long as you don't run it, it will sit there doing nothing."

1 - Make a back up copy of the original workbook in case something goes terribly wrong.
2 - Open the VBA editor using Alt-F11.
3 - Click on Insert...Module
4 - Paste the code below into the pane that opens.
5 - Change the string DropDownSheet to the name of the sheet that contains the drop down. Leave the quotes around the sheetname. You should have to make 3 changes.
6 - Run the code

The reason I'm suggesting that you put the code in a Module (I'll assume it's Module1) is because I'm working on a way to delete that module from each workbook to get rid of the macro. If I figure that out, I'll let you know.

(Since the code isn't in the sheet module that contains the data, you have to refer to each sheet by it's name so that VBA knows which sheets to work with.)

Option Explicit
Sub MakeEmpFiles()
Dim nxtName, fname
 For nxtName = 2 To 102
  Sheets("DropDownSheet").Range("H5") = Sheets("FacNum").Range("A" & nxtName)
   fname = Sheets("DropDownSheet").Range("H7") & "-" _
           & Sheets("DropDownSheet").Range("H5")
   ActiveWorkbook.SaveAs Filename:=fname
End Sub

Come on back if you have any questions.

Report •

January 21, 2010 at 07:41:58
OK, you can try this if you want:

In your original workbook, place the MakeEmpFiles code I offered earlier in a VBA Module as I described above.

Then while still in the VBA Editor, double click the ThisWorkbook module and paste the Workbook_Open code I've included below into that pane.

When you run the MakeEmpFiles macro, each of the 200 workbooks will be saved with both sets of code, but the next time you open any one of them, the code below should delete all code in that workbook, including itself.

Some Notes:

1 – I have commented out the section where the code asks you if you want to remove the code. That means it's going to delete the code automatically, without asking. If you want to be prompted before the code deletes itself, remove the single quote (‘) before the 9 lines in that section of code.

2 – If you put this code in the original workbook, then close and reopen it, it’s going to run and delete itself and you’ll have to start over. Put both sets of code in the original workbook and run the MakeEmpFiles code to create the 200 sheets first.

3 - As mentioned in the code, you need to enable 'Trust Access To Visual Basic Project' by following this menu path in Excel:

Tools...Macro...Security...Trusted Sources and check the box next to "Trust Access To Visual Basic Project" (BTW, my Excel 2003 says Trusted Publishers, not Trusted Sources, but the checkbox is there)

4 - This code will delete all VBA code from the workbook, so if you have code that you want to keep, don't do this!

5 - I did not write the code. I found it here and placed it inside the Workbook_Open event so that it runs autmomatically. You could leave it as a regular macro and just run it manually in each workbook if you want to remove all code from that workbook.

Hope this helps!

Option Explicit
Private Sub Workbook_Open()

     'Trust Access To Visual Basics Project must be enabled.
     'From Excel: Tools | Macro | Security | Trusted Sources
    Dim x               As Integer
    Dim Proceed         As VbMsgBoxResult
    Dim Prompt          As String
    Dim Title           As String
'    Remove single quotes before the next 9 lines to be prompted before deletion
'    Prompt = "Are you certain that you want to delete all the VBA Code from " & _
'    ActiveWorkbook.Name & "?"
'    Title = "Verify Procedure"
'    Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
'    If Proceed = vbNo Then
'        MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
'        Exit Sub
'    End If
    On Error Resume Next
    With ActiveWorkbook.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0

Report •

January 21, 2010 at 07:48:18
Awesome, I will give it a shot! thanks for all of your help!

Report •

January 21, 2010 at 08:17:13
Please make sure you save a backup copy of your original file, perhaps in a folder far away from where you are doing all of this work, in case something goes wrong.

Report •

Ask Question