Excel save selection to pdf macro or VBA

KatP

New Member
Joined
Sep 2, 2020
Messages
17
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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!
 
Upvote 0
That's really helpful thank you. Is it possible to get the macro to select the range of cells to export?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Solution
That is perfect, Thank you very much Domenic. I greatly appreciate it!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top