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]

What was the file format of the original file?
Did the filenames in the A1DDListSource named range contain a file extension?
What was the file format of the new files?

This is the new link to Ron deBruin's SaveAs webpage: Use VBA SaveAs in Excel 2007-2016

 

[rbenkese]

Hi pbornemeier,

Thank you for the quick response -- I should have been more clear in my original question. The Excel file format is the same (and correct).

However, the formatting of the output files changed. For example, all of the blue tabs in the "master" file turn green in the individual output files. In addition, everything that was highlighted in yellow in the original file is now highlighted in a strange orange color. Finally, all of the graphs changed colors.

Is there a way to preserve and maintain the formatting of the file in the code?

Thank you again for the quick reply.

Best,
Ryan

What was the file format of the original file?
Did the filenames in the A1DDListSource named range contain a file extension?
What was the file format of the new files?

This is the new link to Ron deBruin's SaveAs webpage: Use VBA SaveAs in Excel 2007-2016