Need Help populating next available cell.

[Elnicko]

Hi all, I have a fairly basic knowledge of excel, and I'm look for a way to
Populate a set of cells in one specific work sheet, based on data entered in
a master job register work sheet.

I have created one master job register, and 5 seprate sheets for sales staff.

When you fill in the job register, I want it to automatically fill in a sales staffs sheet based on a drop down list in the job register.

Please see attached Excel sheet. Ps how do I upload an excel file to this forum?

Any help or just a starting point would be greatly appreciated.

 

[Elnicko]

This seems to work for mulitple drop downlists
But is there an easier way?

As doing this 1000 time seems crazy

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$9" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD
End If
End If
If Target.Address = "$I$12" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD
 
End If
End If
 
If Target.Address = "$I$15" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD
 
End If
End If
End Sub

 

[Elnicko]

Ok so I've got part of your code working ie when select an value on a drop down list It puts say 0811-825 in the right place.

So thank you so much this is extreamly help full.

The only thing is when I use the next drop down list it replaces the number?

Sub MacroA()
    
    Dim lNextRow As Long
    
    With ThisWorkbook.Worksheets(ActiveCell.Text)
        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & lNextRow).Value = ActiveCell.Offset(, -1).Value
        .Range("E" & lNextRow).Value = ActiveCell.Offset(, -3).Value & ActiveCell.Offset(, -2).Value
        .Range("F" & lNextRow).Value = ActiveCell.Offset(, 1).Value
    End With

End Sub

 

[Elnicko]

I should really try to figure it out for myself before asking questions.
It just exciting to see it working.

I'm sorry.

I have got it all 90% working

Thankyou soo much for your help.

 

[Elnicko]

Ignore Previous post.

Here is the final code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lNextRow As Long, ws As Worksheet, Found As Range
    
    If Target.Count = 1 And Target.Column = 9 Then
        If Target.Row >= 9 And Target.Row <= 1000 And Target.Row / 3 = Int(Target.Row / 3) Then
            If Target.Value <> "" Then
                'Test if Worksheet from drop down list exists
                On Error Resume Next
                    Set ws = ThisWorkbook.Worksheets(Target.Text)
                On Error GoTo 0
                If ws Is Nothing Then
                    MsgBox "Cannot locate a worksheet named " & Target.Text, vbExclamation, "Sheet Does Not Exist"
                Else
                    'Undo
                    For Each ws In Sheets(Array("Paul", "Rachel", "Julija", "Sue", "James"))
                        Set Found = ws.Columns("Z").Find(Target.Address(0, 0), , xlValues, xlWhole)
                        If Not Found Is Nothing Then
                            Found.EntireRow.Delete
                            Exit For
                        End If
                    Next ws
                    ' Log to selected sheet
                    With ThisWorkbook.Worksheets(Target.Text)
                        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & lNextRow).Value = Target.Offset(, -1).Value
                        .Range("E" & lNextRow).Value = Target.Offset(, -3).Value & Target.Offset(, -2).Value
                        .Range("F" & lNextRow).Value = Target.Offset(, 1).Value
                        .Range("B" & lNextRow).Value = ActiveCell.Offset(, 2).Value
                        .Range("Z" & lNextRow).Value = Target.Address(0, 0)
                    End With
                End If
            End If
        End If
    End If
End Sub