Save Excel Range as a PDF

/

Want to save an Excel Range or Workbook as a PDF?

PDFs are a great way to save data to make sure it will not be tampered with in the future.
I have a Auto Email Excel Sheet As PDF tutorial already on the website. There is also a guide on how to Backup Excel Workbooks. The following is pretty much a duplication, but misses out a few steps and gets to the point.

Sub PDFSaver()
'
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim report As String

report = "C:\Users\Chris Norris\Desktop\DataValidation.pdf" ' Change this
'report = Sheets("PDF").Range("A1").Value  - Replace above with this if you want to have save location in workbook - much easier to add a formula to change name based on week etc

    Sheets("test").Select ' Change This
    Range("A1:N39").Select ' Change This
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    report, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

This will overwrite the file, unless you change the name manually. It would be easy to link to the name in a cell, and apply whatever formula you want to make the name dynamically change each time. However if you want to do it with VBA, test this one out!

Public Function UserNamePath()
    UserNamePath = Environ$("UserName")
End Function


Sub PDFSaver()
'
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim report As String
Dim ReportName As String
Dim UserName As String
Dim TimeStamp As String

ReportName = "Secrets of Life" ' Change this
UserName = UserNamePath()
TimeStamp = Format(Now(), "(yyyy-mm-dd hhmm)")

report = ReportName & " - Saved By " & UserName & " at " & TimeStamp


    Sheets("test").Select ' Change This
    Range("A1:N39").Select ' Change This
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    report, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Magic, right?

Same concept can apply to other formats - for example if you want to just backup the workbook use - ThisWorkbook.SaveCopyAs Filename:=”LOCATION“ instead of the PDF paragraph.