VBA to print to default printer and pdf simultaneously

[Dan5977]

Hi,

I have a little script which prints out a selection of pages from a workbook to my default printer.

Private Sub CommandButton1_Click()
    Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
    If WS.Range("$A$1") = "Print" Then WS.PrintOut
      Next WS
End Sub

Different pages will print depending on the parameters of the job, so I have formulas in cell A1 of each sheet to show whether that page is to be printed or not. This all works fine.

I now want to extend the functionality so that at the same time I can create and save a single PDF file containing all of the pages with "Print" in cell A1 in order. Ideally the name of this pdf will also be set to 'Inputs!B3&"_mfg_dwg_pk" - our job no. is located on the 'Inputs' tab in cell B3, so a typical name may be 40850_mfg_dwg_pk. If this is not easily done then we can of course name the PDF when prompted by the PDF software.

Any assistance will, as always, be appreciated. Thanks in advance

 

[Dan5977]

I'm still stuck with this

The spreadsheet should already be saved in the correct place, so ideally the pdf will be created, named and saved in the same folder as the spreadsheet.

I would really appreciate any help.

 

[John_w]

There is code answering a very similar request at https://www.mrexcel.com/forum/excel-questions/1014941-vba-save-pdf-single-file-if-condition-met.html

Just change "A100" to "A1" and change the PDF file name code to:

p = InStrRev(.FullName, "\")
PDFfileName = Left(.FullName, p) & .Worksheets("Inputs").Range("B3").Value & "_mfg_dwg_pk.pdf"

 

[Dan5977]

Thanks John!

I thought I had searched thoroughly but obviously not. Apologies for that and thanks for expanding on it with the file name code. I haven't tried it yet but I'm confident it will work.

 

[Dan5977]

All working with no issues. Many thanks!