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:

Sub Macro1()


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


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

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


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

End Sub

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 the sheet that is defined in the latter part i.e. W8 in the example, 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.

 

[Fluff]

Duplicated https://www.mrexcel.com/forum/excel...l-worksheets-using-vba-macro.html#post5077676

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.