VBA Print to PDF Macro (with specific file name and type)

[CHOPDOG]

Hello, I've added a macro that creates a PDF based on specific cell references. It works as intended, but I would like to make it a little easier for users. I have two questions:

• (1) Is it possible to have the file name already pre-populated to the SaveAsDialog box based on specific cell references (highlighted in yellow)? It currently defaults to blank.
• (2) Is it possible to have the Save as type: pre-set to PDF (red arrow)? It currently defaults to Excel Workbook.xlsx.

The current code and reference image are below. Thanks in advance!

Sub ToPDF()

Dim filesave As FileDialog
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set filesave = Application.FileDialog(msoFileDialogSaveAs)
Set wb = ThisWorkbook
Set ws = wb.Worksheets("A3 Rebate Planner")
Set rng = ws.Range("A1:U37")

Sheets(Array("A3 Rebate Planner")).Select

With filesave

If .Show = -1 Then

rng.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=.SelectedItems(1), _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=True, _
    Quality:=xlQualityStandard

End If
End With
End Sub
[CODE=vba]

[/CODE]

 

[John_w]

Try this - it reads the PDF file name (without the .pdf extension) from V1.

Sub ToPDF()

    Dim filesave As FileDialog
    Dim rng As Range
    Dim PDFfileName As String
    Dim formatIndex As Long, i As Long
    
    Set filesave = Application.FileDialog(msoFileDialogSaveAs)
    
    With ThisWorkbook.Worksheets("A3 Rebate Planner")
        Set rng = .Range("A1:U37")
        PDFfileName = .Range("V1").Value
    End With
    
    With filesave
        .Title = "Save as PDF"
        .InitialFileName = PDFfileName
        formatIndex = 0
        For i = 1 To .Filters.Count
            If InStr(1, .Filters(i).Extensions, "pdf", vbTextCompare) > 0 Then formatIndex = i
        Next
        If formatIndex > 0 Then .FilterIndex = formatIndex
        If .Show Then
            rng.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=.SelectedItems(1), _
                OpenAfterPublish:=False, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=True, _
                Quality:=xlQualityStandard
        End If
    End With
    
End Sub

 

[CHOPDOG]

Thank you so much! Perfect.