Print only selected worksheets

acaPAWN7

acaPAWN7

Board Regular
Joined
Oct 11, 2006
Messages
71
Hye. I'm trying to find a macro that can print selected worksheet only. This is the macro for my current print method:

Code: 
Sub Print_all()
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

As you can see, this function will print all the worksheet in the workbook (my workbook contains 11 worksheets). But I don't want the unnecessary worksheets to be printed. The selected worksheets (to be print) are:

1. PC
2. LD
3. C&M
4. P&L
5. CF
6. NPV&IRR
7. FH
8. Option

Is there any way to modify my macro at the top for specifying the worksheet printings? Hope anyone can help me. Thanks :)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Hello acaPAWN7,
If you just want to print the sheets named in your list then maybe something like this will help.
(I can't test it right now because my printer's on the fritz, but it looks like it should work.)
Code: 
Sub PrintSpecifiedSheets()
Dim Ws As Worksheet, WsArray As Variant, i As Variant
WsArray = Array("PC", "LD", "C&M", "P&L", "CF", "NPV&IRR", "FH", "Option")
For Each Ws In Worksheets
  For Each i In WsArray
    If Ws.Name = i Then Ws.PrintOut Copies:=1, Collate:=True
  Next i
Next Ws
End Sub
 
Upvote 0
Most welcome. Glad it worked. (was wondering if it would . . .) :lol:
I don't like posting untested code.
Even if I know it'll work I still like to test it first.
 
Upvote 0
A little different, using Select Case.
Code: 
Dim ws As Worksheet

For Each ws In Worksheets
  Select Case ws.Name
        Case "PC", "LD", "C&M", "P&L", "CF", "NPV&IRR", "FH", "Option"
            ws.PrintOut Copies:=1, Collate:=True
        Case Else
            ' do nowt
    End Select
Next ws
 
Upvote 0
Code: 
  For Each i In WsArray
    If Ws.Name = i Then Ws.PrintOut Copies:=1, Collate:=True
  Next i
Click to expand...
Hi,

just wondering why you didn't use the "native check", which is application.MATCH
If Not IsError(Application.Match(Ws.Name, WsArray, 0))
you can use
Code: 
Sub PrintSpecifiedSheets()
Dim Ws As Worksheet, WsArray As Variant, i As Variant
WsArray = Array("PC", "LD", "C&M", "P&L", "CF", "NPV&IRR", "FH", "Option")
    For Each Ws In Worksheets
    If Not IsError(Application.Match(Ws.Name, WsArray, 0)) Then Ws.PrintOut Copies:=1, Collate:=True
    Next Ws
End Sub
kind regards,
Erik
 
Upvote 0
Hye. Thanks for the helps. By the way erik, could you explain to me about the native check? I can't see the different between your code and HalfAce's code. Thanks again.
 
Upvote 0
Hi,

there is no difference in result
but instead of a loop the code could use MATCH
I was just wondering why HalfAce (in fact I didn't notice it was yours, Dan :-) ) didn't use the loop: perhaps a version-issue ?

best regards,
Erik
 
Upvote 0
Oh, again thank you very much for the solution. Anyway, can I make a user input for the number of print copy? It should be integrated around this code, right?
Code: 
    If Not IsError(Application.Match(Ws.Name, WsArray, 0)) Then Ws.PrintOut Copies:=1, Collate:=True
    Next Ws

Rather than set it to print only one copy at a time, I would like to have an option (perhaps a userform) for the user to print a number of copies, instead of one. This can be done, right? Thanks for the help again :roll:
 
Upvote 0
change the "1" to "nr"

Code: 
    If Not IsError(Application.Match(Ws.Name, WsArray, 0)) Then Ws.PrintOut Copies:=nr, Collate:=True 
    Next Ws

before write this
Code: 
Dim nr As Integer
nr = Application.InputBox("How many copies do you wish to print?", Type:=1)

perhaps add some errorhandling ...

to avoid "zero" or negatif
Code: 
If nr <= 0 Then Exit Sub

or: zero or negatif number will become 1

Code: 
nr = Application.WorksheetFunction.Max(1, Application.InputBox("How many copies do you wish to print?", Type:=1))
quickly written not checked, but will be close :-)
Erik
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Macro to Select and Print Worksheets Based on Unique Identifier in Worksheet Name
Replies
7
Views
238
JHCali
J
J
  • Question Question
Macro to assign to print button with specific criteria
2
Replies
11
Views
790
Micron
M
P
  • Solved
Export Worksheets into a Single PDF using Criteria on each Worksheet
Replies
7
Views
803
jolivanes
jolivanes
M
  • Question Question
Selective sheet printing
2
Replies
14
Views
886
NateSC
NateSC
E
  • Question Question
Print an Array to PDF only the tabs if Value in Cell is Greater than 0
Replies
4
Views
416
ExcelError
E

Forum statistics

Threads
1,225,218
Messages
6,183,643
Members
453,177
Latest member
GregL65

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top