[VBA] Print to PDF

[sprigelz]

Good day,

I currently have the below VBA code, which runs exactly as I need it to. It goes through a dropdown list and physically prints off the each result.

Sub Macro1()
    Dim r As Range
    For Each r In Range(Range("J2").Validation.Formula1)
        Range("J2").Value = r.Value
        Sheets("Sales-Referrals").PrintOut
    Next
End Sub

What I want to do is update it to instead of printing to a printer, I want it to print each result to a PDF file, save it to the Desktop and name each individual file based off of cell B2.

Any assistance would be greatly appreciated!

 

[John_w]

This macro should do it:

Public Sub Create_PDFs()

    Dim DesktopFolder As String
    Dim r As Range
    
    DesktopFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
    
    For Each r In Range(Range("J2").Validation.Formula1)
        Range("J2").Value = r.Value
        Sheets("Sales-Referrals").ExportAsFixedFormat _
            Type:=xlTypePDF, Filename:=DesktopFolder & Sheets("Sales-Referrals").Range("B2").Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next

End Sub

 

[sprigelz]

Beautiful. Thank you!