I'm trying to write code to have several Sheets in a file printed in one print job.

The Sheets to be printed are created dynamically; their names and the number of sheets differ each time, but I know that I want to print all sheets in the workbook apart from Keep1 and Keep2 (In real 7 different Sheet names). Reason I want to print all sheets in one job is that it could be many sheets and this would mean a long wait and lots of print job pop-ups.

To realize the above I thought of creating a selection of the sheets I want to print and than order to print.

I wrote the following:

Sub printtest()

Dim arr As Variant, sht As Worksheet

arr = Array("Keep1", "Keep2")

Application.DisplayAlerts = False


For Each sht In ThisWorkbook.Worksheets
    If Not UBound(Filter(arr, sht.Name, True, vbtruecompare)) >= 0 Then
        With sht.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
        End With
        sht.Select False
    End If
Next sht

SelectedSheets.PrintOut

Application.DisplayAlerts = True

End Sub

After running the code I run into the following:

1. sht.Select False adds up each Sheet meeting the conditions to the current selection, but since the button is on active sheet Keep1 this sheet is part of the selection (and should not be):

2. The .FitToPagesWide = 1 is performed for each Sheet in the selection, but .FitToPagesTall is also set to 1 (I want to keep this as Automatic, but don't know how to.
3. I don't know how to reference the selection in my print job properly. I tried:

• sht.PrintOut which results in Run-time error 91 (Object variable or With block variable not set).
• SelectedSheets.PrintOut which results ion Run-time error 424 (Object required).

My vba knowledge is limited and I can't find a way to reference the selected pages for the printout.

Thanks for looking into this and explaining what is wrong in this approach.

CodePudding user response：

You could try to make a string with only the worksheet names you want, excluding Keep1 and Keep2. Then take that string into an unidimensional array and use that array as your selection of worksheets:

Dim wk As Worksheet
Dim StringWk As String
Dim ArrayWk As Variant

'string of wk names
For Each wk In ThisWorkbook.Worksheets
    If wk.Name <> "Keep1" And wk.Name <> "Keep2" Then StringWk = StringWk & wk.Name & "|"
Next wk

StringWk = Left(StringWk, Len(StringWk) - 1) 'clean last | delimiter in string

ArrayWk = Split(StringWk, "|")

Sheets(ArrayWk).Select

'code to print to pdf or whatever
'
'
'
'
'

Sheets("Keep1").Select 'deactivate selection


Erase ArrayWk

To create the array we use SPLIT:

Split function

CodePudding user response：

Print Multiple Worksheets

A Few Fixes

• Replace vbtruecompare (there is no such thing) with vbTextCompare.
• Instead of the made-up SelectedSheets (object?) use the Selection property.
• Option Explicit would have immediately warned about these issues. Why don't you use it?

An Improvement

• You rarely need to select anything which is shown in the following code.
• It writes the worksheet names to the keys of a dictionary, which are actually an array, and uses this array (the keys) to reference the worksheets to be printed.

Sub PrintTest()
    
    Dim Exceptions() As Variant: Exceptions = Array("Keep1", "Keep2")

    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    Application.DisplayAlerts = False
    
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, Exceptions, 0)) Then
            With ws.PageSetup
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 2
            End With
            dict.Add ws.Name, Empty
        End If
    Next ws
    
    ThisWorkbook.Worksheets(dict.Keys).PrintOut
    
    Application.DisplayAlerts = True

End Sub