Check table, then copy and paste macro

[jimbobakoo]

What I have is a table of employees names, which site they work at and a comment.

I have a table on another sheet with the addresses of all our sites.

What I need is a macro which will:
>look at the active selection
>copy their name(sheet 1, row B) & comment (sheet 1, row G)
>look up the site they work at (sheet 1, row D)
>copy the relevant address (sheet 3)
>paste the name and addresses into different sheet
>paste the comments into another sheet

This will be used to make printable labels for letters that we post out to employees. They will need to be split up so each name and address is printed on a different label.

The comments will be printed onto letters which will have a headers which is the same for everyone, but would be nice if they could be personalised with the comments of their managers and perhaps their names (something like "Hi John, you have been selected by your manager for your great work, please find enclosed your badge. Jeff's comments: "Comment 1""). But this part is a nice extra, the important part is the address labels.

I don't mind if multiple macros are needed to achieve this... as always please ask if you don't understand/I haven't made things clear enough.