Automate printing. Some help please!

  • Thread starter Thread starter chickooooos
  • Start date Start date
C

chickooooos

Hi

I have a excel work sheet for salary calculation. Sheet1 has database of
employee number, Name, Salary etc. Sheet2 has a nicely formatted pay slip
based on the employee data on Sheet1. I use a pull down menu to select the
Name of the employee and corresponding salary details are displayed in the
pay slip. I then take a print out for the given employee. However it is time
consuming because I have to change the employee name manually for each
person and issue a print command.

Is there a way I can automate this? I mean click a button and excel can
print the pay slip for the entire employees in the database using the pay
slip format in Sheet2?

I would appreciate any pointers in this regard. My knowledge of VBA is very
poor.

Thanks for any help in advance.

Best regards
Aseef
 
Hi Aseef

This is a fairly simple task for VBA - a simple loop down the source sheet
copying data to the relevant cells on the print layout sheet and then
getting it to print automatically.

Assuming that on sheet1 the employee name is in column a & the pay amount is
in column b and the print layout is sheet2 (f6 for name, j10 for amount) you
could do something like,

sub macro1()
dim srcrow as integer

srcrow = 1
worksheets("sheet1").select
while (range("A" & srcrow).value <>"")
worksheets("sheet2").range("f6").value = range("A" & srcrow)
worksheets("sheet2").range("j10").value = range("B" & srcrow)
worksheets("sheet2").print
srcrow = srcrow+1
wend
end sub

This obviously assumes that the print layout is correct (which it must be as
you currently use it) and it will stop processing names when cell("A" &
srcrow) is blank... If you need it to cope with blank lines, it becomes a
bit trickier but let me know.

Kind regards

David
 
Hi

This example will work if you use a Data>Validation as a pull down menu
to select your employee's

Sub test()
Dim cell As Range
For Each cell In Sheets("Sheet1").Range("a1:a20")
'range with employee data (change it to your range)

Sheets("Sheet2").Range("A1").Value = cell.Value
' cell a1 is the cell with the pull down menu

Sheets("Sheet2").PrintOut
' print the sheet

Next
End Sub
 
Back
Top