Subject: Macro Copy&Paste data (Value) based on multiple values

[ebeyert]

Excel 2007/2010
I have the sheet ”Activity” where I record all the activities and the owners for each activity
Sheet: Activity

	A	B	C	D	E	F	G
9	ID	WBS	Activity	Start date	Finish date	Owner	Notes
10	ID1		Action 1			Jan, Paul, Henry
11	ID2		Action 2			Kevin, Paul
12	ID3		Action 3			Eric
13	ID4		Action 4			Tom, Jeanne
14	ID5		Action 5			Jan, Tom
15	ID6		Action 6
16	ID7		Action 7

<tbody>
</tbody>

I have also the sheet WorkPackage which have the same layout as the above sheet.
Sheet: WorkPackage

	A	B	C	D	E	F	G
3			Owner
4			Jan, Paul
5
6
7
8
9	ID	WBS	Activity	Start date	Finish date	Owner	Notes
10			Action 1			Jan, Paul
11			Action 2			Kevin, Paul
12			Action 5			Jan, Tom
13

<tbody>
</tbody>

What I want:
Based on the input (sheet “WorkPackage” cell C4) where I can put in the owner or owners all the activities of these owners needs to be copied from Sheet “Activity” to sheet “WorkPackage”

I am start with

Sub Copy_Data()  Range("A10:G999").Select
  Selection.Copy
  Sheets("WorkPackage").Select
  Range("A10:G999").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

But this is just copy&paste and I have no idea how I can do this based on the owner selection in cell C4

I hope that someone can help me
Thanks in advantage
Ellerd