Printing a long array of excel sheets using VBA

[Stevep4]

I'm trying to use vba to print 54 tabs from a separate xlsx sheet. I've been able to get it to work. The only issue is that the list of 54 tabs is too long and causes an error. I can print, say, 30 tabs without an issue, but 54 is too many. Can I group the tabs at the top of the code? I've done this before with other functions, but never with printing. Below is what I have so far:

Sub PrintFullSetSprint12to18B()
GroupA = Array("Sheet1", "Sheet2", "Sheet3", "etc.")
GroupB = Array("Sheet26", "Sheet27", "Sheet28", "etc.")
Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks("SPRING JUNIOR -12-18 full year.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("GroupA", "GroupB")).PrintOut
    
    End Sub

I get an error on the last line. Does anyone know how I can get both groups to print in one continuous print?

Thanks

 

[Domenic]

Try filling a separate array with your group arrays, and then iterate through the new array to printout each group. Also, here are a couple suggestions...

1) Include the Option Explicit statement at the top of your module. This forces the explicit declaration of variables, and can help with detecting errors.

2) Since you're exiting the sub if the workbook isn't open, check whether it's open before doing anything else. No use doing any, such as assigning values to variables, only to exit the sub when the workbook is not open.

So your code could be amended as follows...

Option Explicit

Sub PrintFullSetSprint12to18B()


    Dim GroupA As Variant
    Dim GroupB As Variant
    Dim AllGroups As Variant
    Dim MyGroup As Variant
    Dim wb As Workbook
    
    On Error Resume Next
    Set wb = Workbooks("SPRING JUNIOR -12-18 full year.xlsx")
    If wb Is Nothing Then
        MsgBox "Workbook is not open!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    GroupA = Array("Sheet1", "Sheet2", "Sheet3")
    GroupB = Array("Sheet26", "Sheet27", "Sheet28")
    AllGroups = Array(GroupA, GroupB)
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    
    For Each MyGroup In AllGroups
        wb.Worksheets(MyGroup).PrintOut
    Next MyGroup
        
End Sub

Hope this helps!

 

[Stevep4]

Thanks so much for your reply. I'm still getting an error at the wb.worksheets(myGroup).printout. I'm going to see if I can figure out why.

 

[BigDawg15]

Not tested but:

Try changing wb.Worksheets(MyGroup).Printout

to

wb.worksheets(AllGroups).Printout


Hope that helps,

BigDawg15

 

[Domenic]

What type of error are you getting?

Can you post the exact code that you're using?

 

[Stevep4]

Thanks for folowing up. The below code does work except for one thing: The page numbers start over for each group. For the GroupA array it shows pages 1-3, and the same with GroupB. I want all 6 tabs to be a single print job with the page numbers running through all the tabs. Thanks again to everyone.

Sub PrintFullSetSprint12to18IWORKS()

    Dim GroupA As Variant
    Dim GroupB As Variant
    Dim AllGroups As Variant
    Dim MyGroup As Variant
    Dim wb As Workbook
    
    On Error Resume Next
    Set wb = Workbooks("SPRING JUNIOR -12-18 full year.xlsx")
    If wb Is Nothing Then
        MsgBox "Workbook is not open!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    GroupA = Array("DEPT 12 TOTAL ", "DEPT 12 OUTERWEAR", "DEPT 12 TOPS")
    GroupB = Array("207 ACTIVE TOPS", "226 DRESSY WOVEN", "234 DRESSY DRESS")
    AllGroups = Array(GroupA, GroupB)
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    
    For Each MyGroup In AllGroups
        wb.Worksheets(MyGroup).PrintOut
    Next MyGroup
        
End Sub

 

[Domenic]

Can you post the original code that gave you the error, confirm the line that caused the error, and the type of error it cause?