Print specific sheets seperately to printer and PDF

[vlacombe]

Is this doable and could someone suggest a script?

(Script would need to be seperate)

1. Print sheets seperately without prompt to the default printer: only if the sheet contains the text: "PrixMax" "6po" "Quote" (Then print them, else ignore)

2. Create a PDF per sheet (If sheet contains the text: "PrixMax" "6po" "Quote" Then create a PDF for each, else ignore) without prompt (like using the microsoft print to PDF "printer" even though it's not the default printer, and send the file(s) and to a specific path written in the script... like a folder on the desktop. Script can use the sheet name as file name and I would want it to overwrite the PDF file if one is existant.

 

[vlacombe]

It doesn't work...

I can see the system loading as if it is working on something, however none of the files are being created in the same folder as of my current workbook, unlike the previous version you suggested.

 

[DanteAmor]

The editor removed the Backslash.
Try this please

Sub Print_sheets()
  Dim sh As Worksheet, i As Long, shs As Variant, w2 As Workbook, sh2 As Worksheet
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  shs = Array("PrixMax", "6po", "Quote")
  For Each sh In Sheets
    For i = 0 To UBound(shs)
      If LCase(sh.Name) Like "*" & LCase(shs(i)) & "*" Then
        Set w2 = Workbooks.Add
        Set sh2 = w2.Sheets(1)
        sh.Cells.Copy
        sh2.Range("A1").PasteSpecial xlPasteValues
        sh2.Range("A1").PasteSpecial xlPasteFormats
        w2.SaveAs Filename:=ThisWorkbook.Path & "[B][COLOR=#0000ff]\[/COLOR][/B]" & sh.Name & ".xlsx"
        w2.Close False
        Exit For
      End If
    Next
  Next
End Sub

 

[vlacombe]

Hi Dante

It did fix the cells with formula but created other problems.
Somehow it changed my sheet format.

I found an alternative solution for me to deal with the formula issue. I changed it before the saving even occurs. It's not ideal for me but the final result is the same.

Thanks a lot for your time

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.