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?
 
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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.