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!

 

[Micron]

For someone like me (quite black and white in interpretations) the details need clarity. I may not be able to provide code solution because of time constraints but at least I think I can help others to help you.

"Print a selection of $A$1:$E$29,$G$33:$L$61"
This range is constant, or is just an example and what you want printed is user selection?

"corresponding sheet"
So if the active sheet is "6", copy a range to sheet 6i?
Where should this copy get pasted - A1?
What if there is data on the sheet already?

Having a button on each sheet means one of a few things:
- you would have a class module that uses With Events so as not to repeat the code 31 times. I've not done much with class modules and zero with With Events so can't help there.
- or repeat the procedure (code) 31 times and hope you don't have to ever edit it. Not the best choice IMO.
- or use one procedure in a standard module and call it via keyboard key combination. That may produce undesirable results if using ActiveSheet. Also, you'd have to be careful to not change existing key combinations. Running from the macro dialog off the ribbon may be safer.
- or use sheet event. Possibly only suitable one for this may be BeforeRightClick. The trick there would be to know if and when the context (right click) menu should be suppressed since you probably don't want the same behaviour in all cases.

Researching 'vba send to specific printer' or similar will provide lots of help for that.

 

[John Moe]

For someone like me (quite black and white in interpretations) the details need clarity. I may not be able to provide code solution because of time constraints but at least I think I can help others to help you.

"Print a selection of $A$1:$E$29,$G$33:$L$61"
This range is constant, or is just an example and what you want printed is user selection?

"corresponding sheet"
So if the active sheet is "6", copy a range to sheet 6i?
Where should this copy get pasted - A1?
What if there is data on the sheet already?

Having a button on each sheet means one of a few things:
- you would have a class module that uses With Events so as not to repeat the code 31 times. I've not done much with class modules and zero with With Events so can't help there.
- or repeat the procedure (code) 31 times and hope you don't have to ever edit it. Not the best choice IMO.
- or use one procedure in a standard module and call it via keyboard key combination. That may produce undesirable results if using ActiveSheet. Also, you'd have to be careful to not change existing key combinations. Running from the macro dialog off the ribbon may be safer.
- or use sheet event. Possibly only suitable one for this may be BeforeRightClick. The trick there would be to know if and when the context (right click) menu should be suppressed since you probably don't want the same behaviour in all cases.

Researching 'vba send to specific printer' or similar will provide lots of help for that.

Thank you for taking the time to hear my problem.

The "print a selection of $A$1:$E$29,$G$33:$L$61" remains constant. Meaning the print button on sheet 1 would print that selection range from sheet 1i, print button on sheet 2 would print that selection on sheet 2i, etc. I was hoping that some code would be able to automate that print command, rather than creating 31 different macros, but if that is the only way I'd be willing to do it.

Regarding "corresponding sheet," nothing needs to be copied and pasted. The corresponding "i" sheets already have the data needed in the selection range above.

You lost me with class module, with events, and keyboard key combinations.

Thank you for the advice, I'm eager to hear solution ideas from anyone willing to assist!

 

[Micron]

Do you want to go to print preview to be sure or just print out? I'm a bit confused about printing a range from a corresponding sheet. Obviously if using a button, the sheet (e.g. 6) will be the active sheet so range to print must refer to the corresponding sheet. Should not be too hard, but it begs the question as to why the button isn't on sheet 6i instead (or any i sheet). To that I figure it's because the sheet is hidden. Is that the case?
Sorry for all the questions. I have started code but need to allow for things like hidden sheets or multiple sheets having been selected, whether on purpose or not.

keyboard key combinations.

A macro can be run via keyboard combination. Look at the option for shortcut key. This can be a way to have one macro that works on the active or selected sheets.

 

[John Moe]

Do you want to go to print preview to be sure or just print out? I'm a bit confused about printing a range from a corresponding sheet. Obviously if using a button, the sheet (e.g. 6) will be the active sheet so range to print must refer to the corresponding sheet. Should not be too hard, but it begs the question as to why the button isn't on sheet 6i instead (or any i sheet). To that I figure it's because the sheet is hidden. Is that the case?
Sorry for all the questions. I have started code but need to allow for things like hidden sheets or multiple sheets having been selected, whether on purpose or not.


A macro can be run via keyboard combination. Look at the option for shortcut key. This can be a way to have one macro that works on the active or selected sheets.
View attachment 115562

This workbook is shared by all supervisors at my work. They are all use to seeing the sheet names of 1-31 representing the date of the month in the bottom scroll. If I put the “i” sheet next to the date sheet, they will mess up all the formulas. If I hide the sheet, excel will not see it to print.

Each sheet is a full 8.5x11 duty roster for the shift on that date. The “i” sheet will be new to everyone and is sized and formatted to auto fill certain info from the date sheet and then it will be printed in a 4x6 index card version of the duty roster to pass out to each employee working that shift.

Therefore, the supervisors will make any changes necessary each shift on the 8.5x11 date sheet, as they do now, and the changes will auto populate on my “i” sheets. I pushed all the “i” sheets to the far right of the bottom scroll, after the date sheets, in the hopes the other supervisors don’t see them and mess with them. That way excel still sees them to print too.

This is why I only want the print button on the 1-31 sheets. I have a print button already for the full size 8.5x11 version, and this second button will be for the index card “i” version.

I can share a sample of the workbook for visual aid, but I’m not back to work until Tuesday night.

Thanks so much for not giving up on me!

 

[Micron]

You could try this. Seems to work but I had to shut down my printer so as to not use resources so I did get a message about that from Windows (I guess). As long as you're connected to a printer you should be ok.

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

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
    ActiveWindow.SelectedSheets.PrintOut Copies:=varInput, collate:=True, _
        IgnorePrintAreas:=False
End If
    

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

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

End Sub

 

[Micron]

Forgot to mention - put that in a standard module, the plan being to write that only once but each button would run that sub with a simple call. Then no need to paste that code 30 times. You will then be able to rely on ActiveSheet since using a button on a sheet will make that sheet the active one. You will not be able to select and print multiple sheets. If that works, I can try tweaking it to deal with the i sheets. So far, this is just a proof of concept to see if the non contiguous range causes unforeseen issues, such as automatic page breaks.

 

[John Moe]

That code printed a limted range of cells from sheet 1, not sheet 1i. Its hard to tell if that will work for the non-contiguous range needed from sheet 1i.

 

[Micron]

I take it that there were no page break issues then. Try adding this in the top section
Dim sht As Worksheet

and inserting the blue changes:

Set sht = Nothing
Exit Sub

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

 

[John Moe]

I'm not sure I entered your suggested edits in the correct area. I got Compile error: Expected End Sub

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"
varInput = InputBox("How many copies to print?")
If IsNumeric(varInput) Then

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

Set sht = Nothing
Exit Sub

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
Exit Sub