Copy Rows from one worksheet to another by entering the Name of the Staff
 

I have 2 workbooks, Staff_BU and Staff.

Now on Staff_BU, I have a cell (call it cell A13). When I enter let
say "Mr Ang" in cell "A13", I want the macro to look for that sam
Character "Mr Ang" on 'Staff' worksheet. under the row "Mr Ang", I wan
to insert this line on Row 13, on Staff_BU. It must start at Row 13.

and after copying the first Staff ID "Mr Ang", the macro is able t
insert another new Staff ID let's say "David" into the next ro
availble, lets say Row 14, and the third Staff ID, the fourth an
continue.

i have the code as below,
i am able to copy and paste the first Staff onto Sheet2, but i have n
idea how to continue the loop to paste the another Staff after row 19
and the third staff at row 20.

any 1 have any ideas how to?

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A13")) Is Nothing Then
Dim GetSht As Worksheet, PutSht As Worksheet, c As Range, r A
String

Set GetSht = Sheet4
Set PutSht = Sheet2

PutSht.Range(Cells(18, 1), Cells(Rows.Count, 4).End(xlUp)).Show

r = 18
For Each c In GetSht.Range(GetSht.Cells(9, 2)
GetSht.Cells(Rows.Count, 1).End(xlUp))
If c = PutSht.Range("A13") Then
GetSht.Range(c, c.Offset(0, 99)).Copy PutSht.Cells(r, 1)
r = r + 1
End If
Next c
Application.CutCopyMode = False
End If
End Su

--
new_to_vb
-----------------------------------------------------------------------
new_to_vba's Profile: http://www.excelforum.com/member.php...fo&userid=3013
View this thread: http://www.excelforum.com/showthread.php?threadid=49820