Thread: Macro to save worksheet as PDF

Hello,

I am looking for a macro that saves page 1 of a worksheet as a PDF and then, if the PDF has to open during the process, for the PDF to close in order to complete other instructions within the macro.

Any help would be awesome.

Thanks in advance!

'same as: http://www.mrexcel.com/forum/showthread.php?p=2850609
Sub Test_PublishToPDF()
  Dim sDirectoryLocation As String, sName As String
  
  sDirectoryLocation = ThisWorkbook.Path
  sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf"
  PublishToPDF sName, activeworksheet
End Sub

Sub PublishToPDF(fName As String, ws As Worksheet)
  Dim rc As Variant
  
  'ChDrive "c:"
  'ChDir GetFolderName(fName)
  rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
  If Not rc Then Exit Sub
  
  ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
  , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
  :=False, OpenAfterPublish:=False
End Sub