Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Need help with batch printing

    I'm not sure if I'm using the corrrect term there or not. I have a spreadsheet that is for employee payroll deductions. They way they've been doing it here is to type out the employee name, enter the amounts for 10 payroll deductions and then print. Then repeat for almost 200 employees. I was hoping there is a way to type out the employees names and deduction amounts into a different sheet and then have the template pull from the list on the other sheet and print out the sheet for each employee without having to type out everything every time we do this. I assume this should work similar to Word's mailings fuction where you have a list of recipients and it prints out as many as are in the list. Any help would be greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    It can be done with code. If you have a table with the names and the 10 deductions the code could loop through the table entering the values into a template and then printing.

    The template could even use a combobox to select an individual from the table to print them individually

    Do you have an example file with a sample table and template sheet?

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    JNeece (2013-07-08)

  4. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,285
    Thanks
    46
    Thanked 255 Times in 235 Posts
    JNeece,

    There are many ways to set this up but essentially, it boils down to the concept of looping as Steve described. You would set it up is like a database table where every column is a field (name, address, phone, salary, deductions 1-2-3..., options, etc.), while every row is a different employee or record. If payroll calculations are performed manually, or in another spreadsheet, they can be incorporated into the sheet to calculate across the fields. Each field would correspond to a blank cell located appropriately in the template. A good way to handle the printing is to make the left most column a column of checkboxes to indicate whether to print or not to print the record. The column should have a header of "Select All" which would provide individual or choice printing. A print button containing code would either print the checked records/template directly or open a form/dialogue box for additional choices/formatting

    Any changes to the employee, whether they are permanent or recurrent changes, should be made in this table so they will only have to be done once or as needed. Adding additional fields would be painless with a slight modification of the code.

    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    JNeece (2013-07-08)

  6. #4
    New Lounger
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have a sample file attached (hopefully). The way it needs to work is on Sheet 1, Cell B1 would = Sheet 2 Column A, Cell C6 would = Sheet 2 column B, F6 = Sheet 2 column C, etc. The actual file will have close to 200 rows for Sheet 2.

    Also, the person who would probably be using this is not very computer savvy so once it's done, will the code be transparent or would the user need to understand it or can they just update Sheet 2 and hit print?
    Attached Files Attached Files

  7. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Try the attached. I named the dataset in Sheet3 EmployeeDataTable (expand the range as needed). I added a combobox which reads the names and enters the selected index value in row 3. Row 3 has the column numbers to lookup the value from the data set. This row can be hidden. When a name is selected from the combobox it fills the values from the table. You can print the name individually if desired

    The "Print All" button loops through the names in the datatable, changing the value and then printing the sheet.

    Steve
    Attached Files Attached Files

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    JNeece (2013-07-08)

  9. #6
    New Lounger
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That works perfect. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •