PDA

View Full Version : Help in VBA Coding (do while / loop until)



gvreddyhr
10-28-2011, 10:18 PM
Hi All,

Hope everyone are doing good, Am in need of your help.

I have created a payroll application using some VBA and worksheet functions as well, everything is working fine, but I am getting struck in pay slip processing, for Payslip creation I created one sheet and used vlookup function, if I key in the employee number, all other particulars will update, and I have created a mail icon which converts that particular range to pdf and generates email, now its becoming very difficult for me to do the same action for all employees.

So am looking where macro automatically runs and generate email for all at a time, below is the more details

Ex:-
Sheet name “payslip” in cell “A5” I need to update the employee number, I have the list of applicable employee numbers in sheet “Maillist” from “a2 to X (xldown / end)”, now I want to write a code where it works as
1. Copy from sheet “Maillist” cell “a2” and paste in sheet “payslip” cell “A5” and then run the mailer code(converts in to pdf and generates email)
2. Copy from sheet “Maillist” cell “a3” and paste in sheet “payslip” cell “A5” and then run the mailer code(generate email)
3. Copy from sheet “Maillist” cell “a4” and paste in sheet “payslip” cell “A5” and then run the mailer code(generate email)
.
.
.
.
.
Copy from sheet “Maillist” cell “a100”(xldown) and paste in sheet “payslip” cell “A5” and then run the mailer code(generate email).


I have the code which automatically convert particular range to pdf and generates email, can anyone help me to get the above code..

Thanks in advance

Regards,
GV Reddy

mancubus
10-29-2011, 04:20 PM
hi.

try...

Dim wsMail As Worksheet, wsPSlip As Worksheet
Dim i As Long, LastRow As Long

Set wsMail = Worksheets("Maillist")
Set wsPSlip = Worksheets("payslip")

LastRow = wsMail.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR
wsPSlip.Range("A5").Value = wsMail.Range("A" & i).Value
' your mailing code here
Next