Print Macro

[JohnSlider]

Sub test()Dim rng As Range
Dim wks As Worksheet


For Each rng In Sheets("Dropdowns").Range("B12:B29")
    If Trim(rng.Value) <> "" Then
        On Error Resume Next
        Set wks = Nothing
        Set wks = Sheets(rng.Value)
        On Error GoTo 0
        If wks Is Nothing Then
            MsgBox "Sheet " & rng.Value & " does not exist"
        Else
            wks.PrintOut
        End If
    End If
Next rng
End Sub

Hello!

So the above macro will print sheets whose names appear in cells within the range of B12:B19 on sheet Dropdowns.

It works a little wonky, in that it will jump to each sheet and print them separately, but I suppose I can live with that.

However, I would like to add a couple features.

1) I would like to be able to designate within a cell on sheet Print how many copies to print out.
2) I would like for it to print double sided.
3) I would like it to print collated.

Are these features that can be built into the above macro? Or perhaps one of you wizards have an even better macro that I should be using.

 

[igold]

Hi,

Aside from the duplex issue, does this solve the collate problem...

Sub Test2()


    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim i As Integer, cpys As Integer, x As Integer
    Dim nam As String
    
    cpys = ws.Range("C12")
    nam = ws.Range("B12")
    i = Sheets(nam).Index
    ThisWorkbook.Worksheets(i).Select
    For x = 13 To 29
        nam = ws.Range("B" & x)
        If Not nam = "" Then
            i = Sheets(nam).Index
            Worksheets(i).Select (False)
        End If
    Next x
    ActiveWindow.SelectedSheets.PrintOut Copies:=cpys, Collate:=True
    ws.Select
    
End Sub