Solved

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

Posted on 2014-10-13
7
87 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
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn how to customize the ribbon and quick access toolbar 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.

773 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