VBA for Printing Sheets

[sswcharlie]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=943m
I am going to use this code which looks good.

If I want to leave 2 sheets out is it best to put in the start of the macro to 'hide' these sheets and reinstate agt the end, or is it better to put in the code , print these except 'sheet1' and 'sheet2'.
The sheets are all set up for print area etc. Sheets have 2 pages and I want both printed in this instance.

Second question is: The sheets I want to print have 2 pages. I only want to print the page 2 's in this instance. if I use above code how do modify to print only page 2 of the sheets.

Your help appreciated.
Thanks
Charlie

[Trebor76]

Hi there,

Not sure why, but the link doesn't work (for me anyway)

Anyway, try the following:

[vba]Sub PrintMacro()

Application.ScreenUpdating = False

For Each Worksheet In ActiveWorkbook.Worksheets

With Worksheet
If .Name <> "Sheet1" Or .Name <> "Sheet2" Then
Sheets(.Name).PrintOut From:=2, To:=2, Copies:=1
End If
End With

Next Worksheet

Application.ScreenUpdating = True

End Sub[/vba]
HTH

Robert

[sswcharlie]

Wow. That is much simpler. I will try out.

" Sheets(.Name).PrintOut From:=2, To:=2, Copies:=1"
With this line does (.Name) Does anything go in here?
With "From:=2" does this mean print 2 pages. How to make page 2?
Thanks

[Aussiebear]

Sheets are named sequentially as in Sheet1, Sheet2, Sheet3....

Therefore if you say From:=2 To:=2 you are referring to Sheet2

[Trebor76]

With this line does (.Name) Does anything go in here?

No, you don't need to do anything here - the code is checking each tab name in the workbook and if it's not 'Sheet1' or 'Sheet2' then it will run the next line of code.

From:=2, To:=2

This piece of code tells Excel to only print page 2 as I thought that's what you wanted (I could be wrong).

[sswcharlie]

Hi

Thanks afor your help guys. I was not very clear to start.


I will be having three macros. One to print both pages. One to print page one only. One to print page 2 only.
If I can get one macro I can work out the other two.

The macro provided will print page 2( I can change the 2 to 1 to print page 1 only) and I just need to know if I put '=from:1,2' and 'to:1,2'
will that work ?
Thanks
Charlie

[Aussiebear]

Sheets are named sequentially as in Sheet1, Sheet2, Sheet3....

Therefore if you say From:=2 To:=2 you are referring to Sheet2

From:=1 To:=2

[sswcharlie]

oops

Have just looked at code again. I see now what happens (from - to etc) So easy!
Will the code above pick up thee default printer ? What code would I need to select a specific printer ?
Cant run the code till I am back in the office Monday.
Thanks to all

Charlie

[Trebor76]

I will be having three macros. One to print both pages. One to print page one only. One to print page 2 only.

So you need two copies of pages 1 to 2, inclusive?

Why not just one macro, i.e. change this line from my orginal macro...

[vba]Sheets(.Name).PrintOut From:=2, To:=2, Copies:=1[/vba]
...to this:

[vba]Sheets(.Name).PrintOut From:=1, To:=2, Copies:=2[/vba]
With regard to changing a printer, I'd record the process to get the code you're after (hopefully).

Robert

[sswcharlie]

Hi Robert
Thanks for the info. I will try macro Monday at work.
Will go for using the default printer code.
Thanks
Charlie

[sswcharlie]

Hi
I have changed the code a little. Used the tab names and added one more.
Code prints ok but in this case does page 2 of all sheets, including the ones shown in If .Name statements.
I get no error messages etc.



[VBA]
Sub PrintMultiple()

Application.ScreenUpdating = False

For Each Worksheet In ActiveWorkbook.Worksheets

With Worksheet
If .Name <> "Printing" Or .Name <> "Factors" Or .Name <> "Template" Then
Sheets(.Name).PrintOut From:=2, To:=2, Copies:=1
End If
End With

Next Worksheet

Application.ScreenUpdating = True

End Sub

[/VBA]


Nearly there. What in the If statement is wrong. Should I use the Tab code and not the name.

Thanks

Charlie

[Trebor76]

Ah, sorry my bad there. The two 'OR' should be 'AND' i.e. try:

Sub PrintMultiple()
 
    Application.ScreenUpdating = False
 
    For Each Worksheet In ActiveWorkbook.Worksheets
 
        With Worksheet
            MsgBox .Name
            If .Name <> "Printing" And _
               .Name <> "Factors" And _
               .Name <> "Template" Then
                    Sheets(.Name).PrintOut From:=2, To:=2, Copies:=1
            End If
        End With
 
    Next Worksheet
 
    Application.ScreenUpdating = True
 
End Sub

I notice that if the name tab isn't one of the three to be excluded, you're still printing one copy of page two of that tab. Is that right?

Regards,

Robert

[sswcharlie]

Hi Robert

Yes correct. I am printing page 2 of each workbook. That part seems ok.

Will try this new code in the office in the morning. Will also look at the code and see the workings of it.
Thanks and regards

Charlie

[sswcharlie]

Hi Robert

Working like magic. Everything doing what it should do.

Thanks for that, much appreciated.

Charlie

[Trebor76]

No problem. I'm glad we were able to provide a solution.

You can remove (or comment out) the 'MsgBox .Name' line of code if you like as I only put that there when I was developing the macro.