Excel VBA macro to print your file to PDF..

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

1 Comment

  • By Daniel, November 12, 2010 @ 4:24 am

    Thanks for the above it is very useful.

    How would I also automatically put a filename for the sheets and location.

Leave a comment