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.
Example:
H5(List of names in drop down)
Bob
Sue
Connie
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
Suggestions?
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 Next End SubPrivate 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 SubWhenever 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.