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