Print all Odd or all Even pages from multiple sheets

[mharper90]

Someone else sent me this code example and it worked perfectly for the task I was working on. He admitted that he didn't know enough about the workings of the code to offer much advice, but was able to find it for me...so thanks!

So I'm using this code for it's intended purpose, but now I'm trying to made a modification (either to it, or as a separate macro) that will allow the same subset of sheets to be printed as either only odd pages (front) or only even pages (back). The best part about this code is that it puts all of the applicable sheets to print into a single print job. There's over 75 double-sided pages to print, so I'm trying to avoid a macro that prints just odd or just even pages, but still sends the job as 75+ different print jobs. Can anyone help?

This procedure prints all sheets except those whose names are passed in as parameters. Example usage:

[COLOR=#000080][FONT=&quot]PrintSheetsExclude false, "Sheet2", "Sheet4", "Sheet6"[/FONT][/COLOR]

This prints all sheets except Sheet2, Sheet4, and Sheet6. The code is shown below:

Sub PrintSheetsExclude(Preview As Boolean, ParamArray Excludes() As Variant)
[COLOR=#008000]'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PrintSheetsExclude
' This prints all sheets except those included as parameters.
' It is legal for Excludes to include sheet names that
' do not exist. If no sheet names are passed in, all
' sheets are printed.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
    Dim Arr() As String
    Dim B As Boolean
    Dim N As Long
    Dim M As Long
    Dim K As Long
    
    ReDim Arr(1 To Sheets.Count)
    For N = 1 To Sheets.Count
        B = True
        For M = LBound(Excludes) To UBound(Excludes)
            If StrComp(Sheets(N).Name, Excludes(M), vbTextCompare) = 0 Then
                B = False
                Exit For
            End If
        Next M
        If B = True Then
            K = K + 1
            Arr(K) = Sheets(N).Name
        End If
    Next N
    If K > 0 Then
        ReDim Preserve Arr(1 To K)
        Sheets(Arr).PrintOut Preview:=Preview
    End If
End Sub

 

[mharper90]

Bumping because I'm in a weird time zone. Thanks in advance for the help!

 

[jmacleary]

Hi there. This will work, but only if each sheet can be printed on a single page. I've added an additional parameter for you to choose front, back or all. It would need to be a lot fancier to cope with multi-page sheets.

Sub PrintSheetsExclude(Preview As Boolean, PrintType As String, ParamArray Excludes() As Variant)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PrintSheetsExclude
' This prints all sheets except those included as parameters.
' It is legal for Excludes to include sheet names that
' do not exist. If no sheet names are passed in, all
' sheets are printed.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Arr() As String
    Dim B As Boolean
    Dim N As Long
    Dim M As Long
    Dim K As Long
    Dim Startfrom As Long
    Dim Skipby As Long
'  PrintType selects which sheets to print. use front, back, or all to choose.
    If PrintType = "front" Then
        Startfrom = 1
        Skipby = 2
    ElseIf PrintType = "back" Then
        Startfrom = 2
        Skipby = 2
    Else
        Startfrom = 1
        Skipby = 1
    End If

    ReDim Arr(1 To Sheets.Count)
    For N = Startfrom To Sheets.Count Step Skipby
        B = True
        For M = LBound(Excludes) To UBound(Excludes)
            If StrComp(Sheets(N).Name, Excludes(M), vbTextCompare) = 0 Then
                B = False
                Exit For
            End If
        Next M
        If B = True Then
            K = K + 1
            Arr(K) = Sheets(N).Name
        End If
    Next N
    If K > 0 Then
        ReDim Preserve Arr(1 To K)
        Sheets(Arr).PrintOut Preview:=Preview
    End If
End Sub

 

[mharper90]

Hi there. This will work, but only if each sheet can be printed on a single page. I've added an additional parameter for you to choose front, back or all. It would need to be a lot fancier to cope with multi-page sheets.

Thanks, I'll give this a try! Just wanted to make sure we're speaking the same about its limitations... All of the applicable sheets to print are identical format, each sheet is the front and back (so 1 page, double-sided per workbook sheet). Are you saying that this code won't account for printing only page 1 of every sheet or only page 2 of every sheet? None of the workbook sheets are more than 2 printable pages.

 

[jmacleary]

Unfortunately, thats the case. It will skip every other worksheet, but as each of your sheets is 2 pages, it will end up printing pages 1 and 2 of the first sheet, 1 and 2 of the third sheet, etc. I can't see a way of picking up just the first pages of ech sheet - maybe someone else can. In the meantime, depending on what printer you are using, several of them have some form of duplexing built into the drivers - it may be worth looking in that direction.

 

[mharper90]

That's what I was afraid of. I'll keep an eye out for other solutions, but otherwise I'll scrap the idea. My work printer has duplex options, but I'm actually creating a file for distribution in several other offices, and since I don't know how all of the printers work, I wanted to provide as many options as possible. Thanks for your help though!