VB script to print drop down list to PDF

[N1njaX55]

I have a spread sheet with a drop down list of users. I would like to create a VB script to be able to select each user, print to a PDF with file name and current date, and have it save to a particular location.
The drop down list is located in cell H2 and the file could be saved in c:\temp. I am brand new to VB and having a hard time figuring out where I'm going wrong. Any help would be appreciated. Thanks!!

 

[John_w]

Assuming the H2 dropdown is a data validation in-cell dropdown, try this macro:

Public Sub Create_PDFs()

    Dim user As Range
    
    With ActiveSheet
        For Each user In Range(.Range("H2").Validation.Formula1)
            .Range("H2").Value = user.Value
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Temp\" & user.Value & Format(Date, " YYYYMMDD") & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
    End With

End Sub

 

[N1njaX55]

I received the following error: Method 'Range' of object '_Worksheet' failed

 

[John_w]

Is my assumption correct? What type of dropdown is it? If it's a data validation dropdown, please show the details of the Data Validation dialogue.

 

[N1njaX55]

Your assumption is correct, it is a data validation dropdown.

 

[John_w]

Please post a screenshot of the Data Validation dialogue so that I can see the DV details. Like this:

 

[N1njaX55]

Here is the screen shot.

 

[John_w]

I can't reproduce the error. I see that H2:I2 are merged cells and the DV List formula is a named range. With my test sheet set up like that the code works for me.