Printing specific excel sheets to PDF - Can I do this without selecting the sheets? 1

[Welshbird]

Hi All,

I have a spreadsheet that has two buttons to print to PDF. One prints one set of pages - the other a different set of pages.

At present it works by selecting the sheets into an array. However, this means that if the user presses 'Print to PDF' and then 'cancel' all of the requisite sheets remain selected. WHich isn't really ideal...

Any thoughts as to a better way to do this?

Option Explicit
Public Sub cmdPDFBuy_Click()
'Stop the screen flickering!
Application.ScreenUpdating = False
'set some variables
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler
'enter name and select folder for file
'start in current workbook folder
strFile = Replace(Replace("BIM_BuyingGroup", " ", ""), ".", "_") _
            & "_" _
            & Format(Now(), "YY_MM_DD") _
            & ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile
'Hardcode sheets to be printed
ThisWorkbook.Sheets(Array("Start", "Current Usage", "Buying Group", "Calculate Profit per Pack", _
        "Price Lists", "Cost Saving Explained", "Profit Calculation Explained", _
        "Clawback & Fees", "Prescribing Information")).Select
'Let the user name the file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")
'Publish to PDF
If myFile <> "False" Then
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
'Ungroup the sheets
Worksheets("Start").Select
End If
exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
Application.ScreenUpdating = True
'Ungroup
Worksheets("Start").Select
End Sub

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[PHV]

What about this ?

...
If myFile <> "False" Then
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End If
exitHandler:
'Ungroup the sheets
Worksheets("Start").Select
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Welshbird]

Perfect! I shoulda thunk of that. Must be that my brain is finally full up.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen