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
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
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.