print multiple copies of a template

[Timehri]

Hi,

We have a template of a labor sheet that we currently print out 50 times per week.
We then manually add all 50 employees #, name and a date range.

Is there anyway to have excel automatically print these sheets out with the #, name and date range included?

Ideally I would like the user to input a date range and the employee number range, and then have the printout include the cross referenced employee name as well.
e.g.
Please input date range: 10/03 to 10/07
Please input employee number range: 011 to 060

Thank you,

Mike

 

[Smitty]

That's entirely doable with VBA.

Where the corresponding list of employee #'s & names? (Sheet name & range).

Smitty

 

[Timehri]

Hi Smitty,

Thanks for the reply.
The list of employee numbers and names is on a separate sheet called 'data'.
Range A1:B50

Mike

 

[Smitty]

See if something like this get5s you started:
<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> PrintTemplate()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> sDate <SPAN style="color:#00007F">As</SPAN> Date
    <SPAN style="color:#00007F">Dim</SPAN> eDate <SPAN style="color:#00007F">As</SPAN> Date
    
        sDate = InputBox("Please enter the Start Date", "Start Date")
        eDate = InputBox("Please enter the End Date", "<SPAN style="color:#00007F">End</SPAN> Date")
        
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Data").Range("A1:A50")
            <SPAN style="color:#00007F">If</SPAN> c <> "" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">With</SPAN> ActiveSheet
                    .Range("A2") = sDate
                    .Range("B2") = eDate
                        <SPAN style="color:#00007F">With</SPAN> .Range("C2")
                            .Value = c
                            .NumberFormat = "00##"
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
                    .Range("D2") = c.Offset(, 1)
                    
                    <SPAN style="color:#007F00">'   For testing, so you don't waste paper!</SPAN>
                    MsgBox .Range("A2") & vbCrLf & _
                    .Range("B2") & vbCrLf & _
                    .Range("C2") & vbCrLf & _
                    .Range("D2")

                    <SPAN style="color:#007F00">'   Uncomment once tested</SPAN>
                    <SPAN style="color:#007F00">'.PrintOut</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            End <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c</FONT>

Just change your ranges to suit.

Smitty

 

[Timehri]

Hi Smitty,

Thanks a lot! It works great!

Is it also possible to only print out for a range of employees? (based on their emp. #)
Like a 'print range', 'print all' or 'print individual' button or something like that?


Thanks,

Mike

 

[Smitty]

How's this:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> PrintTemplate()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> sDate <SPAN style="color:#00007F">As</SPAN> Date, eDate <SPAN style="color:#00007F">As</SPAN> Date
    <SPAN style="color:#00007F">Dim</SPAN> sRng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, eRng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
        sDate = InputBox("Please enter the Start Date", "Start Date")
        eDate = InputBox("Please enter the End Date", "End Date")
        sRng = Application.InputBox("Please enter the Starting Employee Range", "Start Range")
        eRng = Application.InputBox("Please enter the <SPAN style="color:#00007F">End</SPAN>ing Employee Range", "<SPAN style="color:#00007F">End</SPAN>ing Range")
        
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Data").Range("A1:A50")
            <SPAN style="color:#00007F">If</SPAN> c <> "" And c >= sRng And c <= eRng <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">With</SPAN> ActiveSheet
                    .Range("A2") = sDate
                    .Range("B2") = eDate
                        <SPAN style="color:#00007F">With</SPAN> .Range("C2")
                            .Value = c
                            .NumberFormat = "00##"
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
                    .Range("D2") = c.Offset(, 1)
                    
                    <SPAN style="color:#007F00">'   For testing, so you don't waste paper!</SPAN>
                    MsgBox .Range("A2") & vbCrLf & _
                    .Range("B2") & vbCrLf & _
                    .Range("C2") & vbCrLf & _
                    .Range("D2")

                    <SPAN style="color:#007F00">'   Uncomment once tested</SPAN>
                    <SPAN style="color:#007F00">'.PrintOut</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            End <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
        
End <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

 

[Timehri]

Fanatastic!

Thank you so much for your help Smitty!

Mike