Automate printing. Some help please!
 

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

Automate printing. Some help please!
 

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



"chickooooos" wrote in message
...
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

Automate printing. Some help please!
 

Hi

This example will work if you use a DataValidation 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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"chickooooos" wrote in message ...
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