Change from Save As Excel file to Save As PDF

[cookeetree]

G'day Excel Gods,

Could you please assist with changing the below VBA to saving the file as a PDF?
Currently, it hides the empty rows on the RP Concrete worksheet, prints it, then copies it to a new file, pastes the values of formulas and saves it (file name needs to be entered each time).
I just want to hide the empty rows on the RP Concrete worksheet, print it and save the worksheet as a PDF (file name needs to still be entered).

Any help you could provide would be greatly appreciated.
Cheer, Jason.



Sub Print_and_Save_Concrete_Report()

' Select and work within the correct file and sheet
With ThisWorkbook.Worksheets("RP Concrete")

' For each value in "Specimen number" column
For Each Cell In .Range("A16:A24")

' If the value is zero
If (Cell = 0) Then

' Hide the row
Cell.EntireRow.Hidden = True

End If

Next

' Print out the report
.Range("A1:AD35").PrintOut

' Save the report sheet

' Copy the sheet into a new excel workbook
.Copy

' Update the cells so they only contain values, not references
ActiveWorkbook.Worksheets(1).Range("E5:E9").Copy
ActiveWorkbook.Worksheets(1).Range("E5:E9").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets(1).Range("V7:V9").Copy
ActiveWorkbook.Worksheets(1).Range("V7:V9").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets(1).Range("A15:AA24").Copy
ActiveWorkbook.Worksheets(1).Range("A15:AA24").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets(1).Range("H30:H31").Copy
ActiveWorkbook.Worksheets(1).Range("H30:H31").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets(1).Range("N34").Copy
ActiveWorkbook.Worksheets(1).Range("N34").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets(1).Range("AA34").Copy
ActiveWorkbook.Worksheets(1).Range("AA34").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets(1).Range("D35").Copy
ActiveWorkbook.Worksheets(1).Range("D35").PasteSpecial xlPasteValues


' Save the file
Dim reportSaveFileName As String

' Get the filename to save as
reportSaveFileName = "False"
reportSaveFileName = Application.Dialogs(xlDialogSaveAs).Show("S:\Works and Infrastructure Services\Works and Operations\Works and Operations\Soil Lab\REPORTS\ConcreteReport " & Date, 52)

' If (reportSaveFileName = "False") Then
'
' 'MsgBox ("It was false")
' Else
'
' 'MsgBox ("Path: " & reportSaveFileName)
' 'ActiveWorkbook.SaveAs Filename:=reportSaveFileName, FileFormat:=52
'
' End If

' Clear the clipboard
Application.CutCopyMode = xlCopy

' Close the report sheet
ActiveWorkbook.Close Savechanges:=False

' Unhide the cells
For Each Cell In .Range("A15:A23")
Cell.EntireRow.Hidden = False
Range("A1").Select

Next

End With

End Sub

 

[bobsan42]

Sub ExportAsFixedFormat(Type As XlFixedFormatType, [Filename], [Quality], [IncludeDocProperties], [IgnorePrintAreas], [From], [To], [OpenAfterPublish], [FixedFormatExtClassPtr], [WorkIdentity])

ActiveWorkbook.ExportAsFixedFormat xlTypePDF, reportSaveFileName, , True