CODEWITHSUNDEEP
excelvba
P.b
P.b

Reputation: 11415

Select multiple sheets for printing at once

I'm trying to print several Sheets in a file in one print job.

The Sheets 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 sheets).

I want to print all sheets in one job as there could be many sheets, and this would mean a long wait and lots of print job pop-ups.

I thought of creating a selection of the sheets and then order to print.

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

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):
    enter image description here

  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.

  3. I don't know how to reference the selection in my print job.
    I tried:

Run-time error 91 (Object variable or With block variable not set).

Run-time error 424 (Object required).

Upvotes: 2

Views: 648

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

Print Multiple Worksheets

  • 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

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

Upvotes: 2

Related Questions