Pulling contacts to a new sheet automatically

**archie8** · 10-30-2012, 04:41 PM

This may have been answered before but I'm having a hard time figuring out what search terms to use to find it. But here is my problem for those who can look past my lack of searching ability.

I have a list of contacts that I contact for certain projects. Depending on the project, I will send an email to different people each time. The contacts are all in an excel file as a master list. I would like to have a way to where I could quickly select which contacts I want for a certain project and then have a new list be created on the next sheet with all the same info. I figured I could create a column, for example, that I would put a Y in if I wanted to include that person. Then I would run a function/macro which would copy each person's info across multiple columns and paste that into the next spreadsheet. So in the end, the new sheet will have a list of only those contacts I want and I can add more info to the list without sifting through the huge contact list.

Thanks for any ideas.

**JBeaucaire** · 10-30-2012, 04:54 PM

You can use the Autofilter for that.

1) Go down your column putting in Y or X for the contacts you want.
2) Turn on the Autofilter on that column, filter for Y
3) Copy all visible data to another new sheet
4) Turn off the autofilter and clear the column for next time.

You should be able to record that into a macro pretty simply.

**archie8** · 10-31-2012, 08:09 AM

Thanks, that should do it.

**JBeaucaire** · 10-31-2012, 10:41 AM

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

**archie8** · 10-31-2012, 11:02 AM

I am having an additional problem with this. I got the macro to work for the most part but when it is added to the second sheet I am having trouble getting it to format as a table correctly. I have a few column headings set up in the second sheet before I add the new data. Then I grab the data from the first sheet and paste it to the right of the new columns. That is fine. My new question is: can I have excel automatically format all the data as a table (with colors and so I can sort by each column if necessary)? I can get it to work with a set number of cells that I can just record into the macro but since the number of entries copied over changes each time I need a way to only format the cells where there is data.

The code generated from the macro that I used to create a table style for the defined cells is:

    Range("A1:Q7").Select
    ActiveSheet.ListObjects("Table8").TableStyle = "TableStyleMedium2"

A1 is always my starting point and Q is always the ending column but the row for Q changes. Can I change the 7 into a variable that represents the total number of rows?

**JBeaucaire** · 10-31-2012, 11:08 AM

Let's see your code, might help me grasp your problem.

**archie8** · 10-31-2012, 11:22 AM

I edited it into my post above.

So basically, say you have a table of unknown length. All I want to do is use excel's 'format as table' tool to use a table style and define headers. Since that length changes each time I use this, I was thinking you could define a variable that would represent the total number of rows. Then use this variable for the ending range of a selection to format the table.

**JBeaucaire** · 10-31-2012, 12:30 PM

Dim a Long variable, use it to store the value of the last used row, then use that in your processes:

Dim LR as Long





    LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("A1:Q" & LR).Select
    ActiveSheet.ListObjects("Table8").TableStyle = "TableStyleMedium2"