Printing a long array of excel sheets using VBA

Stevep4

New Member
Joined
Aug 28, 2015
Messages
34
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:

Rich (BB code):
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
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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...

Code:
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!
 
Upvote 0
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.
 
Upvote 0
Not tested but:

Try changing wb.Worksheets(MyGroup).Printout

to

wb.worksheets(AllGroups).Printout


Hope that helps,

BigDawg15
 
Upvote 0
What type of error are you getting?

Can you post the exact code that you're using?
 
Last edited:
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top