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

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

One way might be to put a pivot table on sheet 2 and use code to
restrict the name displayed.

Record a macro to see how it might be done.

And you'll probably need some kind of drop-down to select the name.
The easiest way is to attach a name to the range on sheet 1 representing all
the staff names,
than put a Data Validation in a cell - restrict the validation to a list
=MyStaffList.

start with this and let us know if you need more assistance...
--
steveB

Remove "AYN" from email to respond
"countryfan_nt"
wrote in message
news:countryfan_nt.1rtnve_1120777568.9421@excelfor um-nospam.com...

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