Printing 2 sided pages in Excel

[ssbsparky]

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&quot.Select
Range("J5&quot.Select
If RetVal2 > 0 Then
Sheets("Rpt 2&quot.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Sheets("weekly&quot.Select
Range("J6&quot.Select
If RetVal3 > 0 Then
Sheets("Rpt 3&quot.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

 

[joerd]

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.