find several words/names in a column and cut and paste row to another workbook or page

I have a workbook that has a column with differing names and then in row an address - some are called andy jones, some are fred doe, some are julie smith  etc. etc.

I need to run a macro to which I can add the "chosen christian name (s)"  as there may be one or more I need to such as julie & john, and the macro then finds the fields that include the christian name, then cut and past that row which includes address into sheet 2 of the worksheet.

Can anyone help?
andy-patAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
I'm restating your question in order to ensure I understand your request.

You have a column of names (specifically, first and last names separated by a space).  You want a macro that will prompt you to enter a specific name - or list of names (separated by some delimiter, such as a comma).  It will then iterate through the column of names and copy each row where there is a name match to Sheet2 of the worksheet.

Is this correct?  If so, what column contains the names?

-Glenn
0
andy-patAuthor Commented:
Hi Glenn, yes you are correct in your understanding, but not comma deliminated, ie simply

Fred smith               1 new street               somewhere         this country
Jo Bloggs                   2 somehere road     a town                  a country
Harry Ramsden      27 The Street              a city                    over here
julie smith                1b the drive              a town                   over there

So if the vba/macro ran for Smith and Bloggs it would pull row 1,2 and 3 over to a seperate page/worksheet

Hope that explains it!
0
Glenn RayExcel VBA DeveloperCommented:
I understood that the column of names would be space delimited (i.e., first name, space, last name).  However, you need to specify how you will list more than one name in your selection criteria.  I'm assuming comma-separated.  In your example:
example prompt
-Glenn
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

andy-patAuthor Commented:
OK just to explain the search would be for
Just a surname or Christian name so no space/comma needs to be considered. Ie search term would be for any name smith....... But I might want to search for multi names like both smith and Jones and morris to list all those names and addresses with those 3 names. Thanks for looking at and hopefully assisting
0
Glenn RayExcel VBA DeveloperCommented:
This code will prompt the user to enter a name or list of names (separated by comma) to search.  It will then look through the list of names in column A on Sheet1.  If a name match is found, it will copy that entire row to the next available row on Sheet2.
Option Explicit
Sub Copy_Selected_Name_Data()
    Dim rng As Range
    Dim cl As Object
    Dim strNameList As String
    Dim arrNames() As String
    Dim lngLR As Long
    Dim x As Integer
    
    strNameList = InputBox("Enter name(s) to search (separate multiple names by commas):", "Copy Selected Rows")
    If strNameList = "" Then Exit Sub
    arrNames = Split(strNameList, ",")
    
    Sheets("Sheet1").Select 'change "Sheet1" to name of data source sheet
    Application.ScreenUpdating = False
    
    'change "Sheet2" to name of the destination sheet
    lngLR = Sheets("Sheet2").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
    
    Set rng = Range("A2", Range("A2").End(xlDown))
    For Each cl In rng
        For x = 0 To UBound(arrNames)
            If InStr(1, cl.Value, arrNames(x), vbTextCompare) > 0 Then
                cl.EntireRow.Copy
                Sheets("Sheet2").Range("A" & lngLR).PasteSpecial
                lngLR = lngLR + 1
            End If
        Next x
    Next cl
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Sheets("Sheet2").Select
    Range("A2").Select
End Sub

Open in new window


Example macro-enabled file is attached.

Regards,
-Glenn
EE-Q28536812.xlsm
0
andy-patAuthor Commented:
worked like a dream ... thank you so much
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.

If my solution was acceptible, please properly close this question by clicking the "Accept this solution" link above my previous post.  That will ensure that future searches for this type of function will show the solution.

With Thanks,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.