Please can someone help with a formula or VBA script.
I have one workbook (payslip sort) that contains several sheets (one sheet per person that contains a breakdown of their pay) - I have another workbook (payslip macro) that contains a list of names in column A and next to their names in Column B their email address.
Is it possible to have a code that looks up the name on each sheet in (payslip sort) which can be find in A2 (see below example) - and search for that name in the other worksheet (payslip macro) (the list at present located in sheet2 - however this can be moved if that is a problem or even added to the main worksheet) - and copy the email address to i1 in payslip sort.
Payslip sort
[TABLE="width: 943"]
<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]P/E Date[/TD]
[TD]Shift[/TD]
[TD]Company[/TD]
[TD]Location[/TD]
[TD]Units[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD="colspan: 3"]zak@bbc.com[/TD]
[/TR]
[TR]
[TD]Zak [/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]NA - Day[/TD]
[TD]Constance [/TD]
[TD]xxxxxxxxxxxxxxxxxxxxxxxxxx[/TD]
[TD].50[/TD]
[TD]9.00[/TD]
[TD]4.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zak [/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]NA - Day[/TD]
[TD]Dermott [/TD]
[TD]xxxxxxxxxxxxxxxxxxxxxxxxxx[/TD]
[TD]45.00[/TD]
[TD]9.00[/TD]
[TD]405.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zak [/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]NA - Day[/TD]
[TD]Kelly [/TD]
[TD]xxxxxxxxxxxxxxxxxxxxxxxxxx[/TD]
[TD]1.00[/TD]
[TD]9.00[/TD]
[TD]9.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Payslip macro (sheet2)
[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Zak[/TD]
[TD="colspan: 2"]zak@bbc.com[/TD]
[/TR]
[TR]
[TD]nigel[/TD]
[TD="colspan: 2"]nigel@itv.com[/TD]
[/TR]
[TR]
[TD]barry[/TD]
[TD="colspan: 2"]barry@sky.com[/TD]
[/TR]
</tbody>[/TABLE]
I have one workbook (payslip sort) that contains several sheets (one sheet per person that contains a breakdown of their pay) - I have another workbook (payslip macro) that contains a list of names in column A and next to their names in Column B their email address.
Is it possible to have a code that looks up the name on each sheet in (payslip sort) which can be find in A2 (see below example) - and search for that name in the other worksheet (payslip macro) (the list at present located in sheet2 - however this can be moved if that is a problem or even added to the main worksheet) - and copy the email address to i1 in payslip sort.
Payslip sort
[TABLE="width: 943"]
<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]P/E Date[/TD]
[TD]Shift[/TD]
[TD]Company[/TD]
[TD]Location[/TD]
[TD]Units[/TD]
[TD]Pay Rate[/TD]
[TD]Total Pay[/TD]
[TD="colspan: 3"]zak@bbc.com[/TD]
[/TR]
[TR]
[TD]Zak [/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]NA - Day[/TD]
[TD]Constance [/TD]
[TD]xxxxxxxxxxxxxxxxxxxxxxxxxx[/TD]
[TD].50[/TD]
[TD]9.00[/TD]
[TD]4.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zak [/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]NA - Day[/TD]
[TD]Dermott [/TD]
[TD]xxxxxxxxxxxxxxxxxxxxxxxxxx[/TD]
[TD]45.00[/TD]
[TD]9.00[/TD]
[TD]405.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zak [/TD]
[TD]Mon 29 Jul 2019[/TD]
[TD]NA - Day[/TD]
[TD]Kelly [/TD]
[TD]xxxxxxxxxxxxxxxxxxxxxxxxxx[/TD]
[TD]1.00[/TD]
[TD]9.00[/TD]
[TD]9.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Payslip macro (sheet2)
[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Zak[/TD]
[TD="colspan: 2"]zak@bbc.com[/TD]
[/TR]
[TR]
[TD]nigel[/TD]
[TD="colspan: 2"]nigel@itv.com[/TD]
[/TR]
[TR]
[TD]barry[/TD]
[TD="colspan: 2"]barry@sky.com[/TD]
[/TR]
</tbody>[/TABLE]