Make macro that will pdf, iteratively, from dropdown

[CatyH]

3 tabs involved:
1. Dashboard - displays graph and text dynamically filled upon selection of DataValidation list in cell C1 (on Dashboard).
2. Calc - where the data on Dashboard pulls from. It's an intermediate manipulation of the data that is entered in the 'Enter Local Program Data' tab
3. Enter Local Program Data - where the user enters values per program (varies by state for 1 to maybe 150 programs).

So I need to create a macro that will cycle through all entries in the dropdown, creating a pdf 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 pdf (ideally the file name would contain the program 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!

 

[tonyyy]

CatyH,

Welcome to the Board.

You might consider the following...

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

Cheers,

tonyyy

 

[CatyH]

CatyH,

Welcome to the Board.

You might consider the following...

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

Cheers,

tonyyy

THIS IS AMAZING!!!! Not a single edit to be made - it did EXACTLY what I was dreaming it might.... THANK YOU THANK YOU THANK YOU tonyyy!!!!!

 

[tonyyy]

You're very welcome, CatyH.

And you should thank yourself for providing such a clear explanation of what you needed.

 

[rahul473]

Thank you for this, I had a similar issue and this code worked perfectly. However, I was just wondering if there was any way to make it so all the pdf outputs are consolidated into 1 document when you run the code?

 

[CatyH]

Thank you for this, I had a similar issue and this code worked perfectly. However, I was just wondering if there was any way to make it so all the pdf outputs are consolidated into 1 document when you run the code?

I would think that you'd have some issues with that:
1) you'd have to build a "temporary" file that would be the paste-board pre-save
2) you'd need a way to input or select a title for your file (the beauty of tonyyy's code is that file names come right from the dropdown list!)
3) you'd want headers on individual pages (and therefore page breaks after each 'file') to identify the content of the page(s).

there are probably other issues, but this is what comes to mind. I'll be following, because it would be cool to offer users the ability to have it all in one file! Already I have different buttons for opening/not opening and creating ONE / ALL pdfs which users seem to appreciate.

And all thanks to tonyyy! this continues to be amazing code!