Results 1 to 6 of 6

Thread: Print all Odd or all Even pages from multiple sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2013
    Location
    Pearl Harbor, Hawaii
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Print all Odd or all Even pages from multiple sheets

    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:
    PrintSheetsExclude false, "Sheet2", "Sheet4", "Sheet6"
    This prints all sheets except Sheet2, Sheet4, and Sheet6. The code is shown below:

    Code:
    Sub PrintSheetsExclude(Preview As Boolean, 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
        
        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

  2. #2
    Board Regular
    Join Date
    May 2013
    Location
    Pearl Harbor, Hawaii
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print all Odd or all Even pages from multiple sheets

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

  3. #3
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    523
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print all Odd or all Even pages from multiple sheets

    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 by jmacleary; May 14th, 2019 at 11:30 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  4. #4
    Board Regular
    Join Date
    May 2013
    Location
    Pearl Harbor, Hawaii
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print all Odd or all Even pages from multiple sheets

    Quote Originally Posted by jmacleary View Post
    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.

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    523
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print all Odd or all Even pages from multiple sheets

    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.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  6. #6
    Board Regular
    Join Date
    May 2013
    Location
    Pearl Harbor, Hawaii
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print all Odd or all Even pages from multiple sheets

    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •