Printing Odd/Even Pages in every worksheet

[NCJason]

I am using this macro to print all of the odd or even pages in the active worksheet:

Sub Print_Odd_Even()
    Dim Totalpages As Long
    Dim StartPage As Long
    Dim Page As Integer

    StartPage = 1  '1 = Odd and 2 = Even

    'Or use the InputBox suggestion from Gord Dibben
    'StartPage = InputBox("Enter 1 for Odd, 2 for Even")

    Totalpages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    For Page = StartPage To Totalpages Step 2
        ActiveSheet.PrintOut from:=Page, To:=Page, _
                             Copies:=1, Collate:=True
    Next
End Sub

I am trying to edit the macro code to print all of the odd or even pages in every worksheet in the active workbook without any success.

I tried changing the

 ActiveSheet.PrintOut to ThisWorkbook.PrintOut and ActiveWorkbook.PrintOut

and it still only prints the odd or even pages active worksheet.

What changes do I need to make to print every worksheet in the active workbook?

Thanks,
Jason

[Trebor76]

Hi NCJason,

Welcome to the forum.

Let me know how the following goes.

HTH

Robert

Sub PrintAllWorkSheets()

   Dim iCnt As Integer
   
   For iCnt = 1 To Worksheets.Count
      With Sheets(iCnt)
         .PrintOut
      End With
   Next iCnt

End Sub

[NCJason]

Robert,
That code is a start but it does not fully help me.

I now know how to print the odd/even pages in the selected worksheet and how to print every worksheet in the workbook.

How do I print just the odd (or even) pages in every worksheet in the workbook?

[realniceguy5000]

Hi NCJason,

Maybe?

1.) On the Tools menu, point to Macro, and then click Visual Basic Editor.
2.) On the Insert menu, click Module.

Excel inserts a module called Module1 into your project.
3.) In the Module window, type the following code:

Function OddPrint(lCopies As Long) As Boolean
   Dim i As Long
   Dim j As Long
   Dim lPages As Long
   
   lPages = ActiveWorkbook.Sheets.Count
   
   
   For j = 1 To lCopies
   
      For i = 1 To lPages Step 2
         ActiveWorkbook.PrintOut i, i
      Next i
   Next j
   
   OddPrint = True
End Function

Function EvenPrint(lCopies As Long) As Boolean
   Dim i As Long
   Dim j As Long
   Dim lPages As Long
   
   lPages = ActiveWorkbook.Sheets.Count
   
   For j = 1 To lCopies
   
      For i = 2 To lPages Step 2
         ActiveWorkbook.PrintOut i, i
      Next i
   Next j
   
   EvenPrint = True
End Function

Sub PrintOddEven()
   Dim lCopies As Long
   
    lCopies = InputBox("How many copies?", Default:=1)
   
   If OddPrint(lCopies) = True Then
   
      If MsgBox("Print Even Pages?", vbYesNo, _
            "Printing Even Pages") = vbYes Then
         EvenPrint (lCopies)
      End If
   End If
End Sub

4.) Quit the Visual Basic Editor.
5.) On the Tools menu, point to Macro, and then click Macros.
6.) In the Macro Name list, click PrintOddEven.
7.) Click Run.

Hope it helps, Mike

[NCJason]

Mike,
That code is very close to what I need, but it does not quite work correctly. My workbook has three worksheets in it and each worksheet has two pages.

When I run the macro it prints sheet one page one and sheet two page one but does not print sheet three page one. I am prompted to print the even pages and if I select yes it only prints sheet one page two.

Jason

[Trebor76]

Hi Jason,

See if this works for even and odd page printing for each tab in the workbook:

Sub PrintEven()

    Application.ScreenUpdating = False

    Dim intTabCnt As Integer
    Dim intTotalPages As Integer
    Dim intStartPage As Integer
    Dim intPage As Integer

    intStartPage = 2  '1 = Odd and 2 = Even
    
    For intTabCnt = 1 To Worksheets.Count
    
        With Sheets(intTabCnt)
    
            intTotalPages = _
                Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
        
            For intPage = intStartPage To intTotalPages Step 2
                .PrintOut _
                    from:=intPage, _
                    To:=intPage, _
                    Copies:=1, _
                    Collate:=True
            Next
        
        End With
    
    Next intTabCnt
    
    Application.ScreenUpdating = True

End Sub

Sub PrintOdd()

Application.ScreenUpdating = False

    Dim intTabCnt As Integer
    Dim intTotalPages As Integer
    Dim intStartPage As Integer
    Dim intPage As Integer

    intStartPage = 1  '1 = Odd and 2 = Even
    
    For intTabCnt = 1 To Worksheets.Count
    
        With Sheets(intTabCnt)
    
            intTotalPages = _
                Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
        
            For intPage = intStartPage To intTotalPages Step 2
                .PrintOut _
                    from:=intPage, _
                    To:=intPage, _
                    Copies:=1, _
                    Collate:=True
            Next
        
        End With
    
    Next intTabCnt
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert