CODEWITHSUNDEEP
excelvba
hindotmo
hindotmo

Reputation: 1

Print Visible Sheets As Group Except One

I'm trying to print all visible sheets, except one, as a group, to display total page count in the footer, as opposed to page count of each sheet.

I've tried the .Select method, but I don't have a lot of experience, so not much beyond that, and the code below.

Private Sub PrintAll_Click()
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
    If Wks.Visible = xlSheetVisible Then
        If LCase(Wks.Name) <> "Instructions" Then
            Wks.PrintOut
        End If
    End If
Next Wks
End Sub

I expect the visible pages to be grouped and printed as one print job, to ensure page numbers reflect the entire workbook, while omitting the sheet named "Instructions." The result I get is a separate print job for each sheet.

Upvotes: 0

Views: 203

Answers (3)

TinMan
TinMan

Reputation: 7759

Simply hide the Instructions tab and print out the workbook.

Private Sub PrintAll_Click()
    With ActiveWorkbook.Worksheets("Instructions")
        .Visible = xlSheetHidden
        ActiveWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        .Visible = xlSheetVisible
    End With
End Sub

Upvotes: 0

Gangula
Gangula

Reputation: 7294

In your code, using Wks.Printout will only print one sheet and the for loop, loops it to print all sheets separately.

You can use a for loop to hide the sheet "Instructions" and use the ActiveWorkbook.Printout to print the whole workbook as one.

Please try the below code:

Sub Print_Except_One()

' Hide Instructions sheet

    Dim ws As Worksheet

    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name = "Instructions" Then
            ws.Visible = xlSheetHidden
        End If
    Next    

' Print all sheets

    ActiveWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

'Unhide All sheets

    For Each ws In ActiveWorkbook.Worksheets

        ws.Visible = xlSheetVisible

    Next ws

End Sub

Upvotes: 2

Mikku
Mikku

Reputation: 6654

I think you are looking for something like this:

Private Sub PrintAll_Click()

Dim Wks As Worksheet
Dim arr() As Variant
Dim i As Integer

i = 1
For Each Wks In ThisWorkbook.Worksheets
    If Wks.Visible = xlSheetVisible Then
        If LCase(Wks.Name) <> "Instructions" Then
            ReDim Preserve arr(1 To i)
            arr(i) = Wks.Name
            i = i + 1
        End If
    End If
Next Wks

Sheets(arr).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=ThisWorkbook.path & "\print.pdf", _
        openafterpublish:=False, ignoreprintareas:=False


End Sub

Steps:

  • Check for sheet Name and Save it into Array which needs to be Printed
  • Select all the Sheets in Array
  • Save them all combined as a PDF.

Upvotes: 1

Related Questions