Pass cells from Custom Filter to a Text Box (w concatenation) using VBA Code

I have a "stakeholder directory" that is operated by VBA code that affects a custom filter based on a drop-down. After the filter is complete, I'd like to have VBA code tied to a command button that cycles through all of the visible records of the filter and collects a cell value (an email address) and pastes it into a text box (concatenated by a comma and a space). The purpose is to produce an email list that can then easily be copied and pasted into the "To:" field of an email. I operate Excel 2013 on a Windows 7 machine.

A video describing what I'm attempting to do can be found on YouTube via this URL: http://www.youtube.com/watch?v=Ayi_fcKdqRA

On the attached xlsx file, reference the tab labeled "Sample."

Thank you, very much, for your time and attention.

Appreciatively,
Eric

tigeravatar,

The only worksheet we need to be concerned with is the fourth one, labeled, "Sample." Yes, the table will already be filtered (by way of existing VBA code tied to the drop-down in cell C1, highlighted in yellow). When the user makes a selection from the drop-down in C1, the table is already directed to filter accordingly. The VBA code I am requesting has no need to filter the data again. The VBA code I'm requesting only copies values from the table AFTER it has been filtered.

Once the user makes a selection from the drop-down in C1, the table is designed to filter automatically. At that point, the user would click the command button called "Collect Emails." What I would like is VBA code (tied to that command button) which copies the email address from each of the visible records in the filtered data and pastes those values into a cell (let's say, H1) and the code would concatenate each email address with a comma and a space. Like this: (e.g., [email protected], [email protected], etc.). The text box that is located near the command button called "Collect Emails" will then be linked to H1 and will display all of the concatenated emails from the filtered table.

I hope that description is a bit more clear. If not, I'll produce a quick video that will better describe my objective.

Appreciatively,
Cirenav


tigeravat wrote:
Re: Pass cells from Custom Filter to a Text Box (w concatenation) using VBA Code
Hello Cirenav,

Is the sheet being looked at the 'Data' sheet? Is the drop-down in cell AH6? Will the sheet already be filtered or will the macro need to filter it as part of the button click? Where should the results be output? I don't see a userform or textbox control where they can copy the result from.

tigeravatar,

It seems like we must be close... When I try to run this sub, I get an error. Specifically, "Run-time error '91': Object variable or With block variable not set." The highlighted line is the one that starts "With Intersect...."

Moreover, I'm not sure I understand the need for mentioning A6 anywhere in the code. It is just a blank cell. The first cell with any email-address data in it is B9.

I made an effort to modify it myself and do some research on the Intersect function, but was unfruitful.

I feel we are quite close.

Thank you for your time. I strive to have such VBA fluency, one day.