Pulling contacts to a new sheet automatically

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.

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

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:

Please Login or Register  to view this content.

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?

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

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.

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

Please Login or Register  to view this content.