Print all Odd or all Even pages from multiple sheets

mharper90

Board Regular
Joined
May 28, 2013
Messages
111
Office Version
365
Platform
MacOS
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:
Code:
[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:

Code:
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mharper90

Board Regular
Joined
May 28, 2013
Messages
111
Office Version
365
Platform
MacOS
Bumping because I'm in a weird time zone. Thanks in advance for the help!
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
941
Office Version
2007
Platform
Windows
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.

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

mharper90

Board Regular
Joined
May 28, 2013
Messages
111
Office Version
365
Platform
MacOS
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

Well-known Member
Joined
Oct 5, 2015
Messages
941
Office Version
2007
Platform
Windows
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

Board Regular
Joined
May 28, 2013
Messages
111
Office Version
365
Platform
MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,724
Messages
5,446,146
Members
405,384
Latest member
geowbadyt

This Week's Hot Topics

Top