Give this a whirl...

Sub Test()
Call CopyCells("Sales")
End Sub

Sub CopyCells(ByVal strWordToFind As String)
Dim rngFirst As Range
Dim rngCurrent As Range
Dim rngFoundCells As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet
Dim wksToPaste As Worksheet
Dim rngToPaste As Range

Set wksToSearch = Sheets("Sheet1")
Set wksToPaste = Sheets("Sheet2")
Set rngToSearch = wksToSearch.Cells
Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)
If rngCurrent Is Nothing Then
MsgBox strWordToFind & " was not found"
Else
Set rngFirst = rngCurrent
Set rngFoundCells = rngCurrent.EntireRow
Do
Set rngFoundCells = Union(rngCurrent.EntireRow, rngFoundCells)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngFirst.Address = rngCurrent.Address
rngFoundCells.Copy rngToPaste
End If
End Sub

--
HTH...

Jim Thomlinson


"countryfan_nt" wrote:


Hi,

I have a workbook with two sheets:

Sheet 1 "Data": It shows all the information of staff.

__A______B___________ C_____ D___ E
1 STAFF__CARCOLOR__YEAR__AGE__GRADE
2 BOB____BLACK______1999__25___ SM
3 BOB____RED________2000__ 22___MG
4 STEVE__BLUE________2001__ 23___AGM
5 DAVE___WHITE_____ 2002___20___AM
6 BOB____BLACK______2001___28___GM
7 MIKE___GREEN______2003___25___MG

SHEET 2: "RESULTS PAGE"
WHERE THE RESULT APPEARS.

I need to create a macro in the sheet two where when i choose any name.
The macro will filter and copy the enitre rows for the name I specify.
Eg. I want to see all the rows that belong to Bob?

Please help me.
Thanks,
Nawaf


--
countryfan_nt
------------------------------------------------------------------------
countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051
View this thread: http://www.excelforum.com/showthread...hreadid=385422