×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Printing 2 sided pages in Excel

Printing 2 sided pages in Excel

Printing 2 sided pages in Excel

(OP)
I am in need of some help with a little codeing in VB for excel.  I only have limited VB experience but can usually plow through it.  The program was not originaly created by me so I still fumble through some of it.

I am trying to get Excel to print a variable number of pages, but instead of cycling through each one and printing it seperately I need it to print it as 1 print job so I can set it to print the document 2 sided.
Origionaly the macro was set to print this way, the check being if there was a Number in J5 Rpt 2 will print etc.

        Sheets("weekly").Select
            Range("J5").Select
            If RetVal2 > 0 Then
                Sheets("Rpt 2").Select
                ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            End If
        Sheets("weekly").Select
            Range("J6").Select
            If RetVal3 > 0 Then
                Sheets("Rpt 3").Select
                ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            End If

I tried puting it in an array to print it all at once, and I dont know if I am making a simple VB error or what the problem is (Im more used to C++).

            If RetVal2 > 0 Then
                Counter = Counter + 1
                RetValArr(Counter) = "Rpt 2"
            End If
            If RetVal3 > 0 Then
                Counter = Counter + 1
                RetValArr(Counter) = "Rpt 3"
            End If
...
...
            Sheets(RetValArr()).Select
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Im getting an error on  Sheets(RetValArr()).Select  and I cant find a way to print a range like  Sheets(RetValArr(1:Counter)).Select  or something like that.

Any help/advice would be greatly appreciated

Sparky

RE: Printing 2 sided pages in Excel

You need to add Replace:=True at the first occasion where you Select a sheet, and Replace:=False at the next occasions, to extend the selection. As an example, the following code looks to see if the value of [A1]=1, and then includes that sheet in the selection:

Sub hoi()
Dim s As Worksheet, FirstMatch As Boolean
FirstMatch = True
    For Each s In ActiveWorkbook.Worksheets
        If s.Range("A1").Value = 1 Then
            If FirstMatch Then
                s.Select Replace:=True
                FirstMatch = False
            Else
                s.Select Replace:=False
            End If
        End If
    Next s
    If FirstMatch Then
        MsgBox "Nothing to print"
    Else
        ActiveWorkbook.PrintPreview
    End If
End Sub

You can probably workout your own solution with this

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources