Solved

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

Posted on 2014-10-13
7
83 Views
Last Modified: 2014-10-16
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?
0
Comment
Question by:andy-pat
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
 

Author Comment

by:andy-pat
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:andy-pat
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
 

Author Comment

by:andy-pat
Comment Utility
worked like a dream ... thank you so much
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now