Copy email address in column dependant on another column

I need huge help! I have programmed a document and taken me months when some of you guys could do it in an hour but I like a challenge! I am now banging my head of my desk so need help because I don't know where to start and even searched formulas so back to VBA.

I have about 30 sheets where Column J has a list of email addresses in 28 of them! I want to copy all those email addresses to another sheet (DistributionList) only if Column K has the word Yes in the same row.


Any help would be appreciated. I am not sure even if the code goes in each sheet or the work book - It a question for another time how I did my other coding!

N1k.

It doesn't matter where you put this code, it will run on all the sheets.

Also, you didn't specify what column on DistributionList you wanted the email address copied to, so adjust ws1.Cells(OpenRow, "A") accordingly. Right now it is set to column A, as indicated by the "A"

Please Login or Register  to view this content.

Hi Walruseggman,
Apologies for not stating which column on DistributionList I wanted but to be honest at this point I don't mind where it goes so long as it goes

I can read and understand the code mostly which I wanted to do before using. I entered it onto my vba in 'ThisWorkbook" and in "DistributionList" sheet then googled the error message I got but there seems to be so many fixes (tried a couple) that I am unsure what to do and at times what they are talking about!

Message error: Object doesn't support this property or method. I seem to remember something about this from my time long ago in school but I have no idea and only did the vba basics which my employers seem to think I am a fully qualified programmer now lol

If you can offer any suggestions to solve my problem, that would be great.

Thank you for your time.

Annette

Hi Hectop,
I can also read this code and it makes sense to me but if I hit run my computer goes into freeze and I have to restart the program I have no idea why! I tried a couple of times and at one point I did get a message "Method 'cells' of object_worksheet failed. Again I do not know where to correct this.
Thanks
Annette

Syntax error on my part; forgot to add Set. Change the ws1 = line of code to read:

Please Login or Register  to view this content.

Hectop, the one I have has client info on it but will cut and past a couple of sheets etc when I am at work tomorrow to let you see. If you have time I would like to see the code you gave me work. I love learning!

That does work Walruseggman, thank you! I put the code in ThisWorkbook where I have a Workbook_Open () code. (it just asks to open on certain sheet) It means my distribution list will be updated each time the workbook opens which is good. I do notice I have duplicates and I have tried a couple of different things to put a RemoveDuplicates in the code you supplied but I am getting tired now so will sleep on it till tomorrow.

I appreciate all the help you guys have given me and will upload a sample book tomorrow Hectop but no laughing at all my other coding efforts

Thanks, A

Sorry I took so long to come back but my little trials of .RemoveDuplicates didnt go too well and I deleted lots of stuff that I had to re do! I have uploaded a sample of what I am doing.

Thanks

Spring Registration 2015TestingDuplicates.xlsm

Well I'm not sure what kinds of problems you're having or why/how lots of stuff was deleted, but you should be able to put the following code at the end of your Workbook_Open sub:

Please Login or Register  to view this content.

I guess I put the RemoveDuplicates in the wrong place, I didn't even notice until another administrator was in the document and there were only a few sheets in there - Oops! Our remote server connection is down (again) but will review what you guys have sent me once I get back on and send the files to there.

Hectop, it would be so much easier if it was in one place and if I got this working I was going to try changing it for the next one. I do not do enough programming skills and only have my basic course but although I love it, I find it frustrating definitely but too old to go back and do an advanced. I will certainly read your program code and make sense of it before I try it out, thank you. I work in Simcoe, live just outside. I moved from the Scotland UK in 2006