Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy rows from one worksheet automatically, ignore rows that are b | Excel Worksheet Functions | |||
Excel Staff Hours Worksheet... | Excel Discussion (Misc queries) | |||
Staff availability worksheet | Excel Discussion (Misc queries) | |||
how many staff have 1 skill, how many staff have 2 skills, etc. | Excel Discussion (Misc queries) |