Printing excel worksheets using VBA macro

[srinimtech]

Hei guys,

I have an excel workbook which contains worksheets, say, W1 to W9.
I recorded a macro to print the sheets and it is working fine to certain extent.

The code is like this:

[I]Sub Macro1()


[/I][I]    Sheets("W1").Select[/I]
[I]    ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True, _[/I]
[I]    IgnorePrintAreas:=False[/I]


[I]    Sheets("W2").Select[/I]
[I]    ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True, _[/I]
[I]    IgnorePrintAreas:=False[/I]

[I]        Sheets("W3").Select[/I]
[I]    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _[/I]
[I]    IgnorePrintAreas:=False[/I]


[I]    Sheets("W4").Select[/I]
[I]    ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True, _[/I]
[I]    IgnorePrintAreas:=False[/I]
[I]
End Sub
[/I]

Question: In the above code, there's no option to define the printer to be used? Let's say, I need to save as pdf sometimes and sometimes it is a hard copy. How can I define a printer?

And, I'm stuck in the following 2 cases.

Case 1:

Say, for sheet W6, I have to select the paper source as "ManualTray". How can I define this?

Case 2:
I have to print EITHER W7 or W8 based on the below condition
If the value in cell A1 of sheet W7 < A2 of sheet W8, then W7 is to be printed. Else, print W8.
How can I achieve this?

I tried the below code:

-----------------------------------------------------------------------------------------------------------------------------------

Sub Macro2PDF_PA()


Dim w1 As Object
Set w1 = Worksheets("W7")
Set w2 = Worksheets("W8")


P1 = w1.Cells(1, 1)
P2 = w2.Cells(1, 1)
If P1 < P2 Then


    Sheets("W7").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False


Else
Sheets("W8").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End If




End Sub

-----------------------------------------------------------------------------------------------------------------------------------
The code is printing sheet W8 only irrespective of the result of the condition.

I'm no expert in VBA. So, just copying code from recorded macros and tweaking it!!

Regards,
Srini.