Often you won’t want to send out an Excel file, your report is the finished article, either there is no need for the reciever to edit in excel or you’re worried that with everyone using different versions of excel it may not look the same on their machine. Recently I discovered that it is possible to save as PDF with Excel VBA.
This has been a great benefit in my quest to “automate everything” as weekly reporting can now save itself as PDF for easy distribution.
The below code (thanks to Stackoverflow where I found the basis for doing this) saves a set of sheets as a PDF with a file name and the date / time.
For my purposes I have a sheet named “Workings” with all my code variables in it. Change the line
Set rngRange = Worksheets("Workings").Range("D2") to the location of a cell where you have the folder and file name that you want to save the PDF as. EG – The cell could contain “C:\MyFiles\Reporting\Homepage Metrics” (without the speech marks) – This would save the PDF in that folder with the file name “Hompage Metrics” appended with the date and time
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select specifies which sheets should be included in the PDF, change this to the sheets you want in the PDF, ensure that you have already set up the print area and layout of each sheet.
Here is the full Code –
Dim strFilename As String
Dim rngRange As Range
'Considering Sheet1 to be where you need to pick file name
Set rngRange = Worksheets("Workings").Range("D2")
'Create File name with dateStamp
strFilename = rngRange.Value & Format(Now(), "yyyymmdd hhmmss")
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strFilename & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _