Excel save selection to pdf macro or VBA

KatP

New Member
Joined
Sep 2, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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...

VBA Code:
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...

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

Change it as desired.

Hope this helps!
 

KatP

New Member
Joined
Sep 2, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
That's really helpful thank you. Is it possible to get the macro to select the range of cells to export?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

New Member
Joined
Sep 2, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try the following macro instead...

VBA Code:
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

New Member
Joined
Sep 2, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
That is perfect, Thank you very much Domenic. I greatly appreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top