A starter: set ranges etc as required
HTH
Sub FindLastName(srchparm)
Dim ws1 As Worksheet, ws2 As Worksheet, c As Variant
Dim inrng As Range, outrng As Range
Set ws1 = Worksheets("Sheet1")
Set inrng = Range("a2:a500")
Set ws2 = Worksheets("sheet2")
Set outrng = ws2.Range("a2")
ws1.Activate
With inrng
Set c = .Find(srchparm, LookIn:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row).Copy outrng
Set outrng = outrng.Offset(1, 0)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub
Sub MyTest()
FindLastName "Cottle"
End Sub
"M.Gallagher" wrote:
Hi,
I have a spreadsheet with about 50,000 contacts. The columns (fields)
are Name, Address, Phone Number, etc..
I need to search through this spreadsheet for all last names that match
a certain criteria, say 'Alfred' and make a copy of this ROW and paste
it to another sheet/workbook (not important which one).
The problem is that the 'Name' field is in the format of 'RW & T
Cottle' or 'M Granger' (just like in the phone book) so as you can see
I CAN'T sort this column by last name and then make a copy of every
entry that matches the search string.
Instead what I need is a spectacular Macro (or something) to do it for
me, otherwise i'd be sitting there for weeks copying and pasting (there
are about 50,000 x 100 files...).
I need to automate this almost completely. I want to enter the name to
search for, have it search for all those records, copy those rows
associated with those records, paste them in a new sheet and that's
all! Sounds so easy hey! Well I dare someone to come up with a
solution!
I would really appreciate some advice in regards to this problem, as it
could mean a promotion! :)
... and a solution would be absolutely awsome!!
Thanks in advance for any help provided.
Michael Gallagher
--
M.Gallagher
------------------------------------------------------------------------
M.Gallagher's Profile: http://www.excelforum.com/member.php...o&userid=31299
View this thread: http://www.excelforum.com/showthread...hreadid=509708