PDA

View Full Version : VBA for Printing Sheets



sswcharlie
05-12-2011, 06:30 PM
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
05-12-2011, 07:49 PM
Hi there,

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

Anyway, try the following:

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
HTH

Robert

sswcharlie
05-12-2011, 11:48 PM
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
05-13-2011, 02:51 AM
Sheets are named sequentially as in Sheet1, Sheet2, Sheet3....

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

Trebor76
05-13-2011, 05:18 AM
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
05-14-2011, 01:49 AM
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
05-14-2011, 05:04 PM
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
05-14-2011, 05:05 PM
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
05-14-2011, 06:01 PM
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...

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

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

Robert

sswcharlie
05-14-2011, 06:53 PM
Hi Robert
Thanks for the info. I will try macro Monday at work.
Will go for using the default printer code.
Thanks
Charlie

sswcharlie
05-15-2011, 08:48 PM
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.




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




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

Thanks

Charlie

Trebor76
05-15-2011, 09:17 PM
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
05-15-2011, 09:26 PM
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
05-16-2011, 02:18 PM
Hi Robert

Working like magic. Everything doing what it should do.

Thanks for that, much appreciated.

Charlie

Trebor76
05-16-2011, 04:28 PM
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.