Save specific sheet as PDF

[JoshLyman]

I'm currently using the below code to allow users to save the active sheet as PDF, as well as renaming it according to the sheet name/date/time. I have it assigned to a button at the end of each individual sheet and it works great. However, I have a main 'Dashboard' sheet that lists all of the other sheets (e.g., Sheet1, Sheet2, Sheet3) and I would like to have a button for each of them on the dashboard that allows the user to save that specific sheet as a PDF in exactly the same way as below. Initially I thought about it just being 1 button which then prompted the user to specify which sheet they wanted to save as PDF, but that's way beyond my capabilities and I am assuming much more difficult.. so a button for each sheet which allows them to click and save it as PDF would work just fine.

Sub PDFActiveSheet()
'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Any and all help would be greatly appreciated.

 

[JoshLyman]

Works perfectly. Thanks for your help and your patience. Really appreciate it.

 

[tahmed5835]

I'm currently using the below code to allow users to save the active sheet as PDF, as well as renaming it according to the sheet name/date/time. I have it assigned to a button at the end of each individual sheet and it works great. However, I have a main 'Dashboard' sheet that lists all of the other sheets (e.g., Sheet1, Sheet2, Sheet3) and I would like to have a button for each of them on the dashboard that allows the user to save that specific sheet as a PDF in exactly the same way as below. Initially I thought about it just being 1 button which then prompted the user to specify which sheet they wanted to save as PDF, but that's way beyond my capabilities and I am assuming much more difficult.. so a button for each sheet which allows them to click and save it as PDF would work just fine.

Sub PDFActiveSheet()
'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Any and all help would be greatly appreciated.

Thank you for sharing this code,
but my file is not work properly