Macro to look up cell in table and paste relevant information.

Hey,

what I have is a list of selected employees, with the site they work at next to them.

What I need is a macro which looks at the site they work at, references it with an address table in a separate worksheet and then pastes the site address along with the employee's name onto a thrid worksheet which will be used to print post labels.

>Look at which site the employee works at (Sheet 1, D:D)
>Find the address of said site (Sheet 3)
>Paste the relevant address along with the employees name (Sheet 1, B:B) into a separate worksheet.

This will be done weekly, and I need the macro to either only work on the latest's weeks table, or to just work with the current active selection.

If you can think of a better way round this problem, a better way to lay out my table, absolutely anything that will help, please say as nothing is set in stone. Also if I haven't explained myself well enough please feel free to ask any questions.

Maybe:

Please Login or Register  to view this content.

That works quite well, I would prefer it to run it for just the active selection though if possible, Also could you have it so it prints the addresses in the format of the attached spreadsheet. As they need to be printed off onto labels sheets, the sheets are set out so there are 2 columns of labels and 7 rows on an A4 sheet, making each label 99mmx38mm. Pretty much exactly the same as the image linked.

I also have some 3x6 sticker sheets (63.5mm x 46.6mm) which I could also use.

Originally Posted by jimbobakoo

I also have some 3x6 sticker sheets (63.5mm x 46.6mm) which I could also use.

Can you provide a row height and column width of your sample per excel measurements?

I'm doing some tests now to find out what sizes I need the rows/cells. It's really difficult, anyone know an easy way of doing this? At the moment the best I have come up with is trial and error.

I have a word document which has it laidd out perfectly, not sure how to get this into excel though

This is pretty much all I have to work with (except for the actual label sheet, which I sent you a picture of.

Oh damn, that's not even the right format, the columns are right, but there are 8 rows instead of 6! I'll try and find a relevant one, I have the sheets in front of me so I can provide any real life measurements

This one works, I just tested and everything fits on the labels. The word documents seems to have cells, is there anyway this could be copied over to an excel document?