Printing an Array of xlsx worksheets from a separate xlsm file

[Stevep4]

I'm trying to figure out code that will print out specific worksheets from an xlsx file from an xlsm file. Let's say the file I want to print sheets off of is Print.xlsx and I want to print tabs Sheet1 and Sheet2. I created an xlsm file and have this code:

Sub PrintPages()
 Dim wb As Workbook
    Set wb = Workbooks.Open("S:\Business Analysis\Print.xlsx"")
    Application.Dialogs(xlDialogPrinterSetup).Show
    wb.Worksheets(Array("Sheet1", "Sheet2")).PrintOut
End Sub

This code actually works. Only, it opens the file I want to print from. For several reasons, I just want the code to establish Print.xlsx and print the desired tabs, not open the workbook, as it's already open. So, the set wb above is really causing me problems. If I take out the Open, it doesn't work. I'm not sure how to simply establish Print.xlsx without opening it.

Thanks

 

[Domenic]

The following macro first checks whether Print.xlsx is opened. If so, it proceeds to print your array of worksheets.

Sub PrintPages()

    Dim wb As Workbook
    
    On Error Resume Next
    Set wb = Workbooks("Print.xlsx")
    If wb Is Nothing Then
        MsgBox "Workbook is not open!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    
    wb.Worksheets(Array("Sheet1", "Sheet2")).PrintOut
    
End Sub

Hope this helps!

 

[Stevep4]

Thanks! You're a life saver!