Look at this example:
http://en.allexperts.com/q/Excel-105...-FIND-COPY.htm
Hopefully you learn a thing or two by doing this yourself...I'll give you
the modified code just in case you are new to VBA...
Sub CopyCA()
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")
Dim sRow As Long
Dim dRow As Long
Dim sCount As Long
sCount = 0
dRow = 0
For sRow = 1 To Range("A65536").End(xlUp).Row
If Cells(sRow, "A") Like "*CA*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
End If
Next sRow
MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"
End Sub
HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"Angus" wrote:
Hi,
i have data like following, which is the salesman responsible for each state
until a specific time:
David - CA - 2008/1/12
Peter - NY - 2008/5/25
Susan - CA - 2009/3/25
David - NY - 2008/4/20
How do I search "CA" and it will show latest records for each salesman has
been working in CA, ie,
David - CA - 2008/1/12
Susan - CA - 2009/3/25