For 2007 and up:
In excel 2007 there is a built-in option: File / Save & Send / Create PDF & XPS Document.
The macro below does that for you, faster and easier. Assign it to a button and change the sheets array and references to ActiveSheet to have a standardized button.
I use this to print 3 sheets to 1 PDF in a specific report.
Sub Save_as_pdf() Dim FSO As Object Dim s(1) As String Dim sNewFilePath As String Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet1").Activate Set FSO = CreateObject("Scripting.FileSystemObject") s(0) = ThisWorkbook.FullName If FSO.FileExists(s(0)) Then '//Change Excel Extension to PDF extension in FilePath s(1) = FSO.GetExtensionName(s(0)) If s(1) <> "" Then s(1) = "." & s(1) sNewFilePath = "D:\file.pdf" '//Export to PDF with new File Path ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=sNewFilePath, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False End If Else '//Error: file path not found MsgBox "Error: this workbook may be unsaved. Please save and try again." End If Set FSO = Nothing Sheets("Names").Select Row = Row + 1 Wend Sheets("Funnel").Select Call MsgBox("All PDF files have been created!") End Sub
For Excel 2003 & older:
100% free solution to printing your excel file to PDF.
First of all you need the CutePDF. This program acts like a printer on your computer.
Now after installing CutePDF, when you run this macro, you will get a pop-up to name your PDF file, and select a location to save it.
So this is not a solution if you are planning to save lots and lots of files, but it worked great for me.
Sub Print_To_PDF() '=================================================== 'Select all visible sheets, except for SOURCE DATA '=================================================== For Each ws In Sheets If ws.Name <> "SOURCE DATA" And ws.Visible Then ws.Select (False) Next '=================================================== 'Print to PDF '=================================================== Application.ActivePrinter = "CutePDF Writer on CPW2:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub