Excel save selection to pdf macro or VBA

[KatP]

Hello,

I need to create a button to save a selection of cells on a worksheet in excel as a pdf. Can anyone help with this?

Many thanks,
Kat

 

[Domenic]

The following macro first checks to make sure that a range of cells is selected, then prompts the user to specify the path and filename, and then exports the selected range of cells to PDF...

Option Explicit

Sub ExportSelectionToPDF()

    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells, and try again!", vbExclamation, "Selection"
        Exit Sub
    End If
   
    Dim saveas_filename As Variant
    saveas_filename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & ActiveSheet.Name & ".pdf", _
        filefilter:="PDF (*.pdf), *.pdf", _
        Title:="Save As", _
        ButtonText:="Save")
       
    If saveas_filename = False Then Exit Sub
   
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveas_filename
   
    MsgBox "Completed...", vbInformation, "Completed"
   
End Sub

Note that the following line of code determines the initial path and filename...

 InitialFileName:=Application.DefaultFilePath & "\" & ActiveSheet.Name & ".pdf", _

Change it as desired.

Hope this helps!

 

[KatP]

That's really helpful thank you. Is it possible to get the macro to select the range of cells to export?

 

[Domenic]

Sure, which range of cells do you want to export? Is it always the same range of cells? And is it always from the active worksheet? Or from a particular worksheet?

 

[KatP]

Hi Domenic,

It would be everything in columns A:P and yes it is always the same range of cells from the same active worksheet.

 

[Domenic]

Try the following macro instead...

Option Explicit

Sub ExportRangeToPDF()

    If TypeName(ActiveSheet) <> "Worksheet" Then
        MsgBox "No worksheet is active!", vbExclamation, "Worksheet"
        Exit Sub
    End If
    
    Dim saveas_filename As Variant
    saveas_filename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & ActiveSheet.Name & ".pdf", _
        filefilter:="PDF (*.pdf), *.pdf", _
        Title:="Save As", _
        ButtonText:="Save")
        
    If saveas_filename = False Then Exit Sub
    
    Dim last_row As Long
    last_row = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim export_range As Range
    Set export_range = Range("A1:P" & last_row)
    
    export_range.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveas_filename
    
    MsgBox "Completed...", vbInformation, "Completed"

End Sub

Hope this helps!

 

[KatP]

That is perfect, Thank you very much Domenic. I greatly appreciate it!

 

[Domenic]

You're very welcome, cheers!