Thread: Copying rows (content & formats) to separate worksheet based on cell value

Hello,

I am looking to automatically copy rows from the first worksheet 'Master List' into separate worksheets. The first worksheet 'Master List' contains many co-workers and who they are assigned to maintain relationships with. Based on the names of the co-workers in Column A (starting in row 16), I would like the rows to be copied into the following worksheets based on the name. For example, everytime a row is added in the 'Master List' with the name 'Childs' in column A, I would like it to be added to the worksheet named 'Childs' (starting at row 16 in the worksheet).

Below is the worksheet. Can someone please help me figure this solution out??

Thanks!

Example - Tracking Report (2).xls

Hi bobbyjay,

Generally, I try to use an event handler to move data over to new sheets. This prevents confusion over what get's moved and what doesn't. As an example, I included a bit of code to be added to the Master worksheet module (right click on the tab name Master List, and choose view code).

The following code activates when you double click any cell within the data subsect. It copies data to the proper sheet after the double click event. The code can be modified to accept different events. Let me know if that helps.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Crow As Long
    Dim ws As Worksheet
    Dim nm As String
    Dim lRow As Long
    
    Crow = Target.Row
    
    If Crow >= 16 Then
        Cancel = True
        nm = Cells(Crow, 1).Value
        Set ws = Sheets(nm)
        lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        If lrpw < 16 Then
            lRow = 16
        End If
        
        Rows(Crow).Copy ws.Cells(lRow, 1)
        
    End If
    
    
    
End Sub