VBA Printing

[wid2001]

I have an excel Workbook with multiple sheets. I am trying to figure out the code in vba to print two specific worksheets to pdf or straight to the default printer, duplex (Sheet 1 on front and sheet 2 on back. Any help would be appreciated.

 

[XopieX20]

Here is something that will save to PDF and your printer.

Sub PrintOUts()
Dim Printx As String
Dim sCurrentPrinter As String

Setting the current printer to the active printer
sCurrentPrinter = ActivePrinter

Sheets(Array("Sheet1", "Sheet2")).Select 'This is where you change the sheets if you need to
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "INSERT FILE PATH AND NAME OF DOC HERE) & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True


'Notice it has Sheets(ARRAY), this is where you would list all the sheets you want to print
ActivePrinter = sCurrentPrinter
Printx = InputBox("How many copies would you like?")
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).PrintOut Copies:=Printx 'Change worksheets here you want to print








End Sub

 

[wid2001]

Thanks for the reply. I tried using that and was unable to get it to work.

 

[XopieX20]

What is going on with it? Do you get an error? I know it works because I use it myself.

 

[XopieX20]

So sorry... I left out a ( in the code... Use this below. I just tried it and it works... Make sure on the ARRAY part you replace my SHEET1 and SHEET2 with your own sheet(s)

Sub PrintOUts()
Dim Printx As String
Dim sCurrentPrinter As String


'Setting the current printer to the active printer
sCurrentPrinter = ActivePrinter


Sheets(Array("Sheet1", "Sheet2")).Select 'This is where you change the sheets if you need to
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ("FILEPATH\NAME OF DOC") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True




'Notice it has Sheets(ARRAY), this is where you would list all the sheets you want to print
ActivePrinter = sCurrentPrinter
Printx = InputBox("How many copies would you like?")
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).PrintOut Copies:=Printx 'Change worksheets here you want to print


End Sub

 

[wid2001]

That fixed the Printer part of the code perfectly. Thanks!

 

[XopieX20]

Happy to help!