Print only selected worksheets

[acaPAWN7]

Hye. I'm trying to find a macro that can print selected worksheet only. This is the macro for my current print method:

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

 

[acaPAWN7]

Okay, but how's the overall code? How am I going to integrate these two codes?:

Between

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:=nr, Collate:=True
    Next Ws
End Sub

and with this?

Dim nr As Integer
nr = Application.InputBox("How many copies do you wish to print?", Type:=1)
If nr <= 0 Then Exit Sub
nr = Application.WorksheetFunction.Max(1, Application.InputBox("How many copies do you wish to print?", Type:=1))

Thanks~

 

[HalfAce]

This should be all you need.

Sub PrintSpecifiedSheets()
Dim nr As Integer, Ws As Worksheet, WsArray As Variant, i As Variant
nr = Application.InputBox("How many copies do you wish to print?", Type:=1)
If nr <= 0 Then Exit Sub
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:=nr, Collate:=True
  Next Ws
End Sub

 

[acaPAWN7]

Thanks. By the way, for the 'P&L' and 'Option' worksheet, I have two pages for both. After I print, I realized the two pages are printed at both sides of the paper. How can I make it to print at one side only? Because it is easier to view the report rather than have to flip back the page.

 

[erik.van.geit]

Okay, but how's the overall code? How am I going to integrate these two codes?:

did you try something before asking ?
as copies needs to be defined before printing, it is obvious the inputboxline needs to be before the "worksheet-loop" !

to print at one side only, you would need to change your printer-settings, but I've no experience and didn't do any testing about this

 

[acaPAWN7]

Now the problem is solved. The setting for each worksheet is different after I check the print setting for every worksheet. I unchecked the "print for both page" option and it prints at one side only. Thanks.


Off topic:
I want to create a link that can go to any previous worksheet from the active worksheet. Does anyone knows the code? I tried to use the 'Previous' macro code, but it seems an error on it.

 

[erik.van.geit]

to return to the previously selected sheet
try this

Global previousSheet As Worksheet

Sub goto_previous_selected_sheet()
On Error Resume Next
previousSheet.Select
End Sub

assign this macro to a shortcutkey

in the workbookmodule

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set previousSheet = Sh
End Sub

kind regards,
Erik

 

[acaPAWN7]

Just now I tried to put your code. Which folder I need to put for both codes? I assigned the the first code in the Modules folder and assigned shortcut key on it. And for the second code, I assigned it in 'ThisWorkbook' under the Microsoft Excel Objects folder? Is it that way?

For the code:

Global previousSheet As Worksheet
Sub goto_previous_selected_sheet()
On Error Resume Next
previousSheet.Select
End Sub

It seems that a single line has separated between the code "Global previousSheet As Worksheet" and "Sub goto_previous_selected_sheet()". Can you explain to me why there is a single line between both codes?? It should be a single-function macro, right?

But it think the code works (from what I experienced just now).

 

[erik.van.geit]

everything correct

the empty line between declaration of the variable and the procedure is a layout-question
global variables need to be declared outside procedures

if you want, you can insert another procedure in between without problem
you could even declare the global variable in another (general) module

find Global in the helpfiles

hmmm... seems like I cannot find the relevant info myself ...
take a look at Public then

kind regards,
Erik