Export only one sheet as PDF

[damnfabio]

Hello

Thanks for the help in advance!

I wanted to have a button on a landing page of a workbook to print a specific sheet named "leather letter" on the excel file.

So basically I would like that when someone clicks that button a prompt shows up to select the location to print only that sheet in pdf.

Can someone help me with this? I've searched everywhere and couldn't find a good answer!

Thank you

 

[6StringJazzer]

connect your button to this code:

Public Sub SheetToPDF()

   Dim DestFolder As String
  
   DestFolder = GetFolder
   If GetFolder <> "" Then
      Worksheets("leather letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:=DestFolder & "\Leather Letter.PDF"
   End If

End Sub

Function GetFolder() As String

   Dim fldr As FileDialog
  
   Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
   With fldr
       .Title = "Select a Folder"
       .AllowMultiSelect = False
       '.InitialFileName = strPath
       If .Show <> -1 Then GoTo NextCode
       GetFolder = .SelectedItems(1)
   End With
  
   Set fldr = Nothing

End Function

 

[damnfabio]

connect your button to this code:

Public Sub SheetToPDF()

   Dim DestFolder As String
 
   DestFolder = GetFolder
   If GetFolder <> "" Then
      Worksheets("leather letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:=DestFolder & "\Leather Letter.PDF"
   End If

End Sub

Function GetFolder() As String

   Dim fldr As FileDialog
 
   Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
   With fldr
       .Title = "Select a Folder"
       .AllowMultiSelect = False
       '.InitialFileName = strPath
       If .Show <> -1 Then GoTo NextCode
       GetFolder = .SelectedItems(1)
   End With
 
   Set fldr = Nothing

End Function

Hi!!

Thank you so much for you reply! I really appreciate your help.

I've tried the code above but it's giving me the error in attach.

Any suggestion?

 

[6StringJazzer]

I'm very sorry, replace that function with this one:

Function GetFolder(Optional Title As String = "Select folder", Optional InitialFolder As String) As String

   With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      .Title = IIf(Title = "", "Select a Folder", Title)
      .ButtonName = "Select Folder"
      .InitialView = msoFileDialogViewDetails
      .InitialFileName = IIf(InitialFolder = "", ActiveWorkbook.Path, InitialFolder)
    
      If .Show <> -1 Then
         GetFolder = ""
      Else
         GetFolder = .SelectedItems(1)
      End If
   End With

End Function

 

[damnfabio]

I'm very sorry, replace that function with this one:

Function GetFolder(Optional Title As String = "Select folder", Optional InitialFolder As String) As String

   With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      .Title = IIf(Title = "", "Select a Folder", Title)
      .ButtonName = "Select Folder"
      .InitialView = msoFileDialogViewDetails
      .InitialFileName = IIf(InitialFolder = "", ActiveWorkbook.Path, InitialFolder)
   
      If .Show <> -1 Then
         GetFolder = ""
      Else
         GetFolder = .SelectedItems(1)
      End If
   End With

End Function

Hi Jeff!

This worked perfectly!

Just wondering if there's a way of changing the way the prompt shows up to save the file.

It shows up to select the folder. Is it possible to change the code to present the typical "save as" window?

Thank you

 

[6StringJazzer]

Do you want to allow the user to specify the folder location and the actual file name?

I think there is a way to do what you want, so that it will look like a Save As dialog. But it will be completely different code than what I've provided, starting from scratch.

 

[smozgur]

This worked perfectly!

The marked solution has been changed accordingly.

@damnfabio - in your future questions, please mark the post that answered your question as the solution to help future readers.

 

[damnfabio]

Do you want to allow the user to specify the folder location and the actual file name?

I think there is a way to do what you want, so that it will look like a Save As dialog. But it will be completely different code than what I've provided, starting from scratch.

ahh,

I understand.

Apologies, for not explaining correctly from the beginning.
That would be in fact the best solution.
Are you able to help me with this?