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.

[jimbobakoo]

I have just transposed the address table now, makes it a little easier to copy and paste, the data. Don't mind if you think either way is better, or if you think any format changes will make things easier please suggest... Nothing is set in stone!

[jimbobakoo]

Maybe I put this under the wrong title, I'm pretty sure it's possible. Can anyone point me in the right direction to get this script started?

Like I mentioned in my original post, the comments section isn't necessary. Just:
>looking up the cinema next to their name in the table
>pasting their name & the relevant address into a separate worksheet

I guess I over complicated it in my original post, was just trying to be thorough.

[jimbobakoo]

I think I may have over complicated this issue in my first post and put it under the wrong title, since this thread died pretty quickly I have made a new one which should explain things better... don't post in this thread.


>http://www.excelforum.com/excel-prog...ml#post3701406