Saving a file multiple times using each choice in a drop down list

[Francoise White]

Hi

How can I build a macro to select each choice in a drop down list individually and, after each selection, save the file with the name of the drop down cell selected ? After saving each file, I'd also like to break the links in the saved files but not the main file.

So, for example, the main file has 10 sheets pulling data from other locations based on the name of a branch in cell a1 in sheet 1. Cell a1 is a validated data cell referring to the list of the 50 branch names further down in the same sheet.

All help warmly welcomed.

 

[pbornemeier]

Welcome to the Board!

What do you mean by "drop down list"? list box or combo box? Form Control or ActiveX control? Is the control on a userform or ona worksheet?

What type of links do you want to break?

 

[Francoise White]

Phil

Thank-you for picking this up. Unfortunately I'm not very technical so can't answer in your own terms !

The drop down box is set up via the data, validation, list selection. The list is 60 choices long and is situated lower down on the same tab as the drop down choice in A1.

The links to be broken are to other excel files where the link is via a simple "=[other file]tab name!f1" formula.

 

[pbornemeier]

Are you always going to save the file as 60 other files with names based on the source range for data validation list?

Why does the macro have to select each item on the list?

A macro could just paste the first name on the list into cell A1, then export it then go to the second name and continue on down the list.

 

[Francoise White]

Phil

Yes - you are right. The macro could just paste each one and save that version. The master file needs to keep the drop down list. The idea is to keep the master file centrally and be able to choose any site we wish. The separate 60 files for each site will be emailed to them/saved on a central base.

So what would the macro look like to choose each name and paste ?

 

[pbornemeier]

Create a named range for the list source of the data validation list for cell A1. This code expects the name to be A1DDListSource. If it is likely that the list will change, I suggest you use a dynamic range instead of a hard coded range for the named range.

Code assumes that the validation cell is on worksheet "Sheet1" if that is not the case, make appropriate changes to the sWorksheetName = "Sheet1" line.

There is no error checking for invalid filename characters in the data validation list. They include: \ / : * ? " < > |

The SaveAs line could have some complications. For more info see: http://www.rondebruin.nl/saveas.htm

Lots of good info at his webpage, including info on mailing workbooks/worksheets from using Excel code: http://www.rondebruin.nl/sendmail.htm

Option Explicit
 
Sub ChangeA1CreateNewFile()
    ' Create a named range for the list source of the data validation list
    ' for cell A1.  This code expects the name to be "A1DDListSource"
    ' Code assumes that the validation cell is on worksheet "Sheet1"
 
    Dim rngCell As Range
    Dim sWorksheetName As String
    Dim sNewWorkbookName As String
 
    sWorksheetName = "Sheet1"
 
    For Each rngCell In Range("A1DDListSource")
        Worksheets(sWorksheetName).Range("A1").Value = rngCell.Value
        sNewWorkbookName = rngCell.Value
        ActiveWorkbook.Sheets.Copy 'Copy all worksheets to new workbook
        BreakLinks 'Break all links in the new workbook
        'RemoveNames 'Optional - uncomment to remove names from new workbook
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sNewWorkbookName
        ActiveWorkbook.Close
    Next
 
End Sub
 
Private Sub BreakLinks()
    'Breaks links to other workbooks from the active workbook
 
    Dim vLinks As Variant
    Dim vLink As Variant
    vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(vLinks) Then
        For Each vLink In vLinks
            ActiveWorkbook.BreakLink Name:=vLink, Type:=xlLinkTypeExcelLinks
        Next
    End If
 
End Sub
 
Private Sub RemoveNames()
    Dim nm As Name
 
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
 
End Sub

 

[Francoise White]

Thank you. Your help is hugely appreciated and I will try this out and look up the websites. Thanks again.

 

[AboveBeyond]

Is there a way to save just the active sheet and also hard copy the data?

The current macro saves all worksheets and doesn't hardcode the data.


Thanks!

 

[pbornemeier]

Do you want to save the active sheet to another workbook with no formulas, names or links?

 

[AboveBeyond]

Only the worksheet should be copied over with the name of that worksheet and all formulas/links should be hardcoded.

Thanks for helping!