Print all Odd or all Even pages from multiple sheets

mharper90

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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Bumping because I'm in a weird time zone. Thanks in advance for the help!
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top