Assign Rows of Data to Employees from Master

[AznLife]

I'm attempting to create an assignment spreadsheet. This way someone can assign rows of data to an individual without the need to reference or mess up the master sheet.
I found a VBA option that kind of works as intended however it creates extra steps to make it "automated".

The code below gives me the end result I want, but has errors that popup during process.
For example: when i fill in information B - J, and go to select employee gives me debug error, I have to click end...than select employee from dropdown...and click the cell again to push the rows data to appropriate worksheet. Very basic knowledge on VBA code, manipulated below code to work for my sheet.

I want to fill in information from cells B - J, and once I select an employee from Column A dropdown list it pushes data over to that Employee's worksheet. (27 employees, tried an option where I created a module for each employee and had code to call said module based on input in column A was too intense on computer as it ran through call list every time I entered dropdown.)

Attached screenshot of very basic sheet structure. Employee worksheets will have same header information and they will manually update their own sheets.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    
    'Check if the changed cell is in column A (Assigned To)
    If Target.Column = 1 Then
        'Get the selected worksheet name
        Set ws = Worksheets(Target.Value)
        
        'Get the last row in the selected worksheet
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
        
        'Copy row to selected worksheet
        Set rng = Range("A" & Target.Row & ":S" & Target.Row)
        rng.Copy ws.Range("A" & lastRow)
        
        'Delete the row from Pending Applicants sheet
        rng.Delete Shift:=xlUp
    End If
End Sub

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[AznLife]

I attempted to get XL2BB to work. Tried dropbox, hopefully this works.

Dropbox

www.dropbox.com

 

[mumps]

Try this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With Sheets(Target.Value)
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(1, 19).Value = Range("A" & Target.Row).Resize(1, 19).Value
    End With
    Rows(Target.Row).Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[AznLife]

Try this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With Sheets(Target.Value)
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(1, 19).Value = Range("A" & Target.Row).Resize(1, 19).Value
    End With
    Rows(Target.Row).Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

This works much better than original code. Really appreciate it!

 

[mumps]

You are very welcome.

 

[AznLife]

You are very welcome.

Real quick...if I wanted an option that just copies the data over, but doesn't delete from master sheet is this easy to update? Removing delete line doesn't stop it from deleting.

 

[mumps]

If you delete this line of code, the data will not be deleted.

Rows(Target.Row).Delete

 

[AznLife]

If you delete this line of code, the data will not be deleted.

Rows(Target.Row).Delete

Well...don't I feel silly...that made perfect sense to me but didn't work at first. (I deleted and closed off the space) I deleted everything and re-entered code, now it works perfectly. Pretty sure it was user error, appreciate your help again!

 

[mumps]

You are very welcome. You can now also delete the two lines of code containing “EnableEvents”.