Help w/Macro for PDF Export

[WingSystems]

Hi all,

I have a couple newbie questions for a PDF Export Command Button for a workbook.

Right now I have created a section on a sheet with checkboxes (triggering a TRUE/FALSE value on each sheet) for printing/exporting.


-- removed inline image ---


The macro code for the Print work fine, but the PDF Export is where I run into issues.

Sub PDFExport()
For Each w In Worksheets
If Sheets(w.Name).Range("bt1") = True Then
Sheets(w.Name).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "?????", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True

End If
Next w
End Sub

The issues I am having are below:

• How could I have a Save Prompt or use something like<code> "</code>ThisWorkbook.Path & Filename"? (trying to eliminate hard coding to location due to multiple users).<code></code>
• Upon testing the PDF Export function only takes the first checked sheet above, how could I change my code to select all checked?

Any help at all is appreciated!
-Wing Systems

 

[WingSystems]

-- removed inline image ---

 

[WingSystems]

Attached picture of checkboxes (utilize Cell "bt1" for true value)

 

[Perpa]

WingSystems,
Give this code revision a try:
Perpa

Sub PDFExport()
Dim fPath, ThisFile As String
For Each w In Worksheets
    If Sheets(w.Name).Range("bt1") = True Then
          fPath = ThisWorkbook.Path & Application.PathSeparator
          ThisFile = fPath & w.Name
          Sheets(w.Name).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
          ThisFile & ".pdf", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
          True
    End If
Next w
End Sub

 

[WingSystems]

WingSystems,
Give this code revision a try:
Perpa

Sub PDFExport()
Dim fPath, ThisFile As String
For Each w In Worksheets
    If Sheets(w.Name).Range("bt1") = True Then
          fPath = ThisWorkbook.Path & Application.PathSeparator
          ThisFile = fPath & w.Name
          Sheets(w.Name).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
          ThisFile & ".pdf", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
          True
    End If
Next w
End Sub

Thank you Perpa!

Perhaps I am limited in capablities with PDF exports in Excel, but would you happen to know if it is possible to export the sheets together as one PDF? Currently the output is each sheet separately.

 

[Perpa]

Thank you Perpa!
Perhaps I am limited in capablities with PDF exports in Excel, but would you happen to know if it is possible to export the sheets together as one PDF? Currently the output is each sheet separately.

WingSystems,
You are welcome.
Have a look at the link below. It will give you some idea on how you might proceed 'to export the sheets together as one PDF'.

Eileen's Lounge • View topic - VBA for saving several worksheets to pdf

Here is an excerpt from the link above and the code that they used:
"Set up a macro that will save three selected sheets to one pdf using a cell reference for the filename and allowing the user to enter the save location. I don't want to code in my own file location because I plan on giving this file to my staff to use on their computers. My sheets are named: Timesheet, Expense and Mileage respectivly, and the filename comes from cells A2 and B2 in sheet 'TimeEnter'. "

There were hidden worksheets involved with this thread, I removed that element from the code below.
Perpa

Public Sub SaveSheetsAsPDF()
    Dim wksAllSheets As Variant
    Dim wksSheet1 As Worksheet
    Dim strFilename As String, strFilepath As String
    Dim dlgFolder As FileDialog
    'Set references
    Set wksSheet1 = ThisWorkbook.Sheets("TimeEnter")
    wksAllSheets = Array("Timesheet", "Expense", "Mileage")
    'Set path
    Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With dlgFolder
        .Title = "Select Target Folder Containing Mandate Files"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        strFilepath = .SelectedItems(1) & "\"
    End With
    'Create the full Filename using indicated cells
    With wksSheet1
        strFilename = strFilepath & .Range("A2").Value & " " & .Range("B2").Value & ".pdf"
    End With
    ' Select the sheets
    ThisWorkbook.Sheets(wksAllSheets).Select
    'Save the array of worksheets as a PDF
    ActiveSheet.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=strFilename, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=True
              
    'Deselect all the exported worksheets
    wksSheet1.Select
End Sub