andy-pat
asked on
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?
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?
ASKER
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!
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!
ASKER
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
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
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.
Example macro-enabled file is attached.
Regards,
-Glenn
EE-Q28536812.xlsm
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
Example macro-enabled file is attached.
Regards,
-Glenn
EE-Q28536812.xlsm
ASKER
worked like a dream ... thank you so much
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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