Solved

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

Posted on 2014-10-13
7
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40380144
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
ID: 40380278
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
ID: 40380307
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:andy-pat
ID: 40381028
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
ID: 40382343
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
ID: 40385451
worked like a dream ... thank you so much
0
 
LVL 27

Accepted Solution

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to find and create templates in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

752 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