Creating a macro to export PDFs in a dropdown list

[overwatch]

Hi all,

I have statements I need to generate PDFs for in excel. In my workbook there are 2 sheets, one for the calculation and one that is the statement itself.

The statement sheet needs to be generated into a PDF, it's full of vlookups that reference the calculation sheet and the lookup value is the dropdown cell in the statement sheet.

I need a macro that will export PDFs from the dropdown list, thank you.

 

[John_w]

See if this works for you. I've made a few assumptions: the statement sheet is named "Statement"; the dropdown cell is E2 and is a data validation in-cell dropdown; the PDF files are named "Statement xxxx.PDF" and created in the same folder as the macro workbook, but you can change this to a specific folder as shown in the code.

Public Sub Create_PDFs()

    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim dropdownValue As String
    Dim PDFfile As String, i As Long
    Dim destinationFolder As String
    
    destinationFolder = ThisWorkbook.Path     'Same folder as workbook containing this macro
    'destinationFolder = "C:\path\to\folder\"  'Or specific folder
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
         
    'Cell containing data validation in-cell dropdown
    
    Set dataValidationCell = Worksheets("Statement").Range("E2")
     
    'Source of data validation list
    
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
     
    'Create PDF for each data validation value
    
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With dataValidationCell.Worksheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & "Statement " & dvValueCell.Value & ".PDF", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
        
End Sub