Make macro that will create new .xlsx, iteratively, from dropdown

[Nordmane]

I have a Dashboard that displays graph and text dynamically filled upon selection of DataValidation list in cell A1 (on Dashboard).


So I need to create a macro that will cycle through all entries in the dropdown, creating a new .xlsx with the same formatting for the user.

User will activate macro and be prompted for path/folder in which they would like to store their files. Each dashboard would be a uniquely named .xlsx (ideally the file name would contain the name which is the value selected from the dropdown in the Dashboard.


I have a VERY beginning understanding of vba and haven't written loop code nor done much other than record a macro and tweak the code to my situation. I've seen a few other threads on this topic but couldn't make sense of any of it, so please, if anyone can help, please know that I'm a beginner here. Thank you VERY much for any and all help!

 

[HighAndWilder]

Where does the Dropdown get its list from?

Is each new workbook a copy of the workbook containing the dropdown thereby retaining the same formatting that you mention?

Having multiple workbooks can lead to management issues.

Unles there is a good reason for having these, keeping all of the data / functionality in one workbookk is a good idea.

 

[Nordmane]

Hi,

Thanks for your reply!

I am looking for something like this:

Sub DashboardToPDF()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range

Application.ScreenUpdating = False
'''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
FolderName = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With

'''' Location of DataValidation cell
Set r = Worksheets("Dashboard").Range("C1")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)

'''' Loop through DataValidation list
For Each c In inputRange
r.Value = c.Value
fName = c.Value
'''' Save as pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=FolderName & fName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next c
Application.ScreenUpdating = True
End Sub

The drop down gets it's list from a separate sheet in the workbook and the dashboard is updated using yet another separate sheet containing a detail file.

There is an important reason for having each dashboard in it's own new workbook. This is necessary for our clients.

Thanks so much for reaching out! I appreciate any and all help!

 

[Nordmane]

Instead of PDF, I want xslx files though.