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...

Sub nameSort()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim Name As String
Dim FSpace As String
Dim ws As Worksheet

Application.ScreenUpdating = False
Set ws = Sheets("Contact info") 'Set Destination Worksheet
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
    If InStr(1, Cells(i, "A").Value, "@") > 1 Then   'Identify email address
        NextRow = ws.Cells(Rows.Count, "C").End(xlUp).Row + 1  'find next empty destination row
        Name = Cells(i - 1, "A").Value
        FSpace = InStr(1, Name, " ")
        ws.Cells(NextRow, "B").Value = Left(Name, FSpace - 1) 'first name
        ws.Cells(NextRow, "C").Value = Mid(Name, FSpace + 1)  'last name
        ws.Cells(NextRow, "D").Value = Cells(i, "A").Value   'email
        ws.Cells(NextRow, "E").Value = Cells(i + 1, "A").Value  'phone
        If IsNumeric(Left(Cells(i + 2, "A").Value, 1)) Then     'If there is a second phone #
            ws.Cells(NextRow, "F").Value = Cells(i + 3, "A").Value
            ws.Cells(NextRow, "G").Value = Cells(i + 4, "A").Value
            ws.Cells(NextRow, "H").Value = Cells(i + 6, "A").Value & ", " & Cells(i + 7, "A").Value
        Else                                                    'If there's only one phone #
            ws.Cells(NextRow, "F").Value = Cells(i + 2, "A").Value
            ws.Cells(NextRow, "G").Value = Cells(i + 3, "A").Value
            ws.Cells(NextRow, "H").Value = Cells(i + 5, "A").Value & ", " & Cells(i + 6, "A").Value
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub