Copying cells that meet certain criteria and pasting into columns

[IK_SA]

Hello everyone! I have moderate experience with Excel but new to VBA.
In Sheet 1, I have one column that contains 1000+ cells with names, phone numbers, and email data. I want to make a VBA module that will search each cell in the column, and if the cell meets certain criteria, copy the cell and paste it to columns in Sheet 2.

My Sheet 1 data column looks like this:
John Smith
[email protected]
111-222-3456 cell
222-333-4567 office


Jane Smith
[email protected]
333-444-5678 home

And I want it to be copied into a sortable table in sheet 2:
Name............. Phone................Email
John Smith 111-222-3456 [email protected]
Jane Smith 333-444-5678 [email protected]

How do I write a module that can pick out the cells that meet criteria, copy and paste them into the correct column in Sheet 2? For example, if a cell in Sheet 1 contains an "@" symbol, I want the module to copy and paste that cell to the Email column in Sheet 2. I don't know what criteria I can use for Name, except that each name has at least 3 empty rows that come before it. So in that case, how can I select each Sheet 1 cell that has 3+ empty rows above it, and copy and paste it into the Names column of Sheet 2?

I don't know if my module should be a function, sub, or what... I don't understand how to use most of the code terminology (dim, range, how to indicate that an action is to be performed on Sheet 1 or Sheet 2) in tutorials and similar posts in this forum. I can provide more detail to anyone that can help!
Thank you!

[CAntosh]

The best thing you could to help us help you is to post a small sample of what you're working with. Change all of the private data, obviously, and shorten it down to a handful of entries, but makes sure that the arrangement of the data matches the real thing.

Side question: when there are multiple phone numbers, you only want the first one listed?

[IK_SA]

Yes, just the first phone number. Here is an example file. I want to be able to fill out the columns on the right (First name, last name, etc.) using the data listed in column 1.

[CAntosh]

Give this one a shot...

Please Login or Register  to view this content.