How to auto populate specific data to a different worksheet based off a criteria

[excelnewbie716]

Hi there,

I have worksheet 1 with a list of individual names and their personal details (e.g. location, phone number, interview status etc.). I would like to transfer the names of the individuals whose interview status is "Complete" to worksheet 2. I only need to transfer the name (column A) and the phone number (column B).

Can someone please help me with a macro that will automatically populate this information everytime I refresh the workbook? Also, I will need to manually add new names to the list in worksheet 2 (these names won't appear in worksheet 1) and it's important that these names are not deleted upon refresh.

Thank you in advance!

[Mumps1]

In what column is the status "Complete" entered? How many columns are there? Would you like the copying to take place automatically when you enter "Complete"?

[excelnewbie716]

Hi Mumps1!

The status is in Column F. The last column in the table is H.

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. When you enter the word "Complete" in column F and exit the cell, the data for that individual will be copied to Sheet2.

Please Login or Register  to view this content.

[excelnewbie716]

Hi Mumps1,

I followed your directions but it didn't work for me. Could you take a look at the file attached? I really appreciate your help and patience!

[Mumps1]

Try:

Please Login or Register  to view this content.

[excelnewbie716]

It worked!! Thank you so much. This forum is awesome because of people like you! Wishing you a happy new year

[Mumps1]

It was my pleasure and Happy New Year to you too.