title: VBA Print to PDF and Save with Automatic File Name
body: <p>I have a code that prints a selected area in a worksheet to <code>PDF</code> and allows user to select folder and input file name. </p>
  
  <p>There are two things I want to do though:</p>
  
  <ol>
  <li>Is there a way that the PDF file can create a folder on the users desktop and save the file with a file name based on specific cells in the sheet?</li>
  <li>If multiple copies of the same sheet are saved/printed to PDF can each copy have a number eg. 2, 3 in the filename based on the copy number?**</li>
  </ol>
  
  <p>Here is the code I have so far:</p>
  
  <pre><code>Sub PrintRentalForm()
  Dim filename As String
  
  Worksheets("Rental").Activate
  
  
  filename = Application.GetSaveAsFilename(InitialFileName:="", _
                                       FileFilter:="PDF Files (*.pdf), *.pdf", _
                                       Title:="Select Path and Filename to save")
  
  If filename &lt;&gt; "False" Then
  With ActiveWorkbook
      .Worksheets("Rental").Range("A1:N24").ExportAsFixedFormat Type:=xlTypePDF, _
                                                filename:=filename, _
                                                Quality:=xlQualityStandard, _
                                                IncludeDocProperties:=True, _
                                                IgnorePrintAreas:=False, _
                                                OpenAfterPublish:=True
  End With
  End If
  
  
  filename = Application.GetSaveAsFilename(InitialFileName:="", _
                                       FileFilter:="PDF Files (*.pdf), *.pdf", _
                                       Title:="Select Path and Filename to save")
  
  If filename &lt;&gt; "False" Then
  With ActiveWorkbook
      .Worksheets("RentalCalcs").Range("A1:N24").ExportAsFixedFormat Type:=xlTypePDF, _
                                                filename:=filename, _
                                                Quality:=xlQualityStandard, _
                                                IncludeDocProperties:=True, _
                                                IgnorePrintAreas:=False, _
                                                OpenAfterPublish:=False
  End With
  End If
  
  End Sub`
  </code></pre>
  
  <p>UPDATE:
  I have changed the code and references and it now works. I have linked the code to a commandbutton on the Rental Sheet - </p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim filenamerental As String
  Dim filenamerentalcalcs As String
  Dim x As Integer
  
  
  x = Range("C12").Value
  Range("C12").Value = x + 1
  
  Worksheets("Rental").Activate
  
  Path = CreateObject("WScript.Shell").specialfolders("Desktop")
  
  filenamerental = Path &amp; "\" &amp; Sheets("Rental").Range("O1")
  
  'ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
  Worksheets("Rental").Range("A1:N24").Select
  Selection.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=filenamerental, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False
  
  Worksheets("RentalCalcs").Activate
  
  Path = CreateObject("WScript.Shell").specialfolders("Desktop")
  
  filenamerentalcalcs = Path &amp; "\" &amp; Sheets("RentalCalcs").Range("O1")
  
  'ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
  Worksheets("RentalCalcs").Range("A1:N24").Select
  Selection.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=filenamerentalcalcs, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False
  
  Worksheets("Rental").Activate
  Range("D4:E4").Select
  
  End Sub
  </code></pre>