Macro to assign to print button with specific criteria

[John Moe]

Hi all,

I'm in need of VBA code that I can assign to a print button to print with certain criteria. I can create the button and assign a macro, no problem. I'm just having trouble writing the code.

What I need is a macro that will:

Ask the # of copies to print
Print a selection of $A$1:$E$29,$G$33:$L$61 from the active sheet # plus an i
To a specific printer on my network named: “Printer Name”


I have sheets in my workbook named 1-31 (corresponding to dates in a month). I also have sheets in the same workbook named 1i, 2i, 3i….through 31i. Ideally, the PRINT button would be repeated on all sheets, 1-31, but the command would print the selection on the corresponding sheet 1i, 2i, 3i…

Simply, the print button on sheet 1 would print the selection on sheet 1i, the print button on sheet 2 would print the selection on sheet 2i, etc.

I hope this is understandable!


Thanks!

 

[Sanjeev1976]

You can try the below :

Sub PrintRange()
Dim varInput As Variant
Dim result As Integer
Dim prntArea As String
Dim sht As Worksheet

prntArea = "$A$1:$E$29,$G$33:$L$61"

varInput = InputBox("How many copies to print?")

If IsNumeric(varInput) Then

    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With
    
    Set sht = Nothing
    
    Set sht = Sheets(ActiveSheet.Name & "i")
    sht.Activate
    
    ActiveSheet.PageSetup.PrintArea = prntArea
    ActiveWindow.SelectedSheets.PrintOut Copies:=varInput, collate:=True, _
    IgnorePrintAreas:=False

End If

With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

 

[Micron]

You ended up with an orphaned End With, so I think that's why you got the error. I should have posted the whole thing, which is similar to the above but I prefer my style of indentation. Next time when posting code, please paste within code tags (use vba button on posting toolbar). It's so much easier to follow indented code.

Sub PrintRange()
Dim varInput As Variant
Dim result As Integer
Dim prntArea As String
Dim sht As Worksheet

On Error GoTo errHandler
prntArea = "$A$1:$E$29,$G$33:$L$61" 'edit here if required
varInput = InputBox("How many copies to print?")
If IsNumeric(varInput) Then
'set your own number or just comment out or remove this section
'************
    If varInput > 20 Then
        result = MsgBox("Are you sure you want to print " & varInput & " copies?", vbOKCancel)
        If result = vbCancel Then Exit Sub
    End If
'***********
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False 'this may not be wise
    End With
    ActiveSheet.PageSetup.PrintArea = prntArea
    Set sht = Sheets(ActiveSheet.Name & "i")
    sht.Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=varInput, collate:=True, _
        IgnorePrintAreas:=False
End If
   

exitHere:
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
Set sht = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub