Macro Change Cell Dropdown, Create PDF, Choose Next Dropdown, Repeat

[Bries]

Hello,

I want to automate the process for creating weekly reports. In cell A1 I have a list of 10 departments in a dropdown menu. The rest of the calculations refer to that dropdown to return relevant performance information.

Right now I select Dept 1 in the dropdown menu, save as PDF, select department 2, save as PDF, and repeat for the 10 departments. I can't just create 10 tabs due to issues that are irrelevant to excel.

Is there some macro that can be created that will perform this process for me? It seems like this question was asked at the link below but I don't understand what or how it works, or if it even did end up working. I'm not very experienced in VBA at all: https://www.mrexcel.com/forum/excel-questions/781111-macro-change-name-cell-print-pdf-repeat.html

Is anyone able to walk me through how to solve this?

 

[AFPathfinder]

OK, I think I understand what you're looking for and took a stab at a solution.

Sub ValidationList_PDFPrint()
'Created By:    AFPathfinder
'Created On:    11/21/2017
'Modified On:
'Created For:   Bries
'code example source(s):
'   [URL]https://www.mrexcel.com/forum/excel-questions/781111-macro-change-name-cell-print-pdf-repeat.html[/URL]
'   [URL]https://stackoverflow.com/questions/30603004/iterate-through-an-excel-dropdown-validation-list[/URL]
'------------------------------------------------------------------
    
    Dim inputRange As Range
    Dim cell As Range
    
    Set inputRange = Evaluate(Range("A1").Validation.Formula1)
    
    For Each cell In inputRange
        Range("A1").Value = cell.Value
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users" & Environ("USERNAME") & "\Desktop" & Range("A1").Text & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
    Next cell
End Sub

This is untested, but I believe I've combined your example with Jeanno's solution from Stack Overflow. The variable "inputRange" is set to the list range of cell A1 and the For Each loops through each item in that list. Inside the loop, the value of A1 is changed to the current item in the list and then performs the export to pdf.

I have it saving the file to your desktop with the filename of the text in A1. It is also set to not open the file after exporting (OpenAfterPublish:=False). The part "Environ("USERNAME")" pulls the user's username in case more than one user will run this. If you have any questions on this, let us know.