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.

 

[wigi]

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

Here's one:

Sub Tst()
    
    ThisWorkbook.Worksheets(REFERENCE TO DROPDOWN).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "YOUR VALUE"

End Sub

It will go to the last cell in the sheet you still need to reference correcly with your dropdown. Then it moves one cell down and writes a value in there (change this too).

 

[Elnicko]

Hey thanks for the help, I'm new to macros(i have no idea how to use them)
So I will google some info them.

Do you know if I can up load my excel sheet to this board or how to?

 

[wigi]

Do you know if I can up load my excel sheet to this board or how to?

No, you can't. You can only include HTML versions of your data (layout) and formulas.

The purpose is to properly explain what you need without using file uploads.

For the code: go to VBA. Insert a new module (Insert > Module) and paste code.

Then change the code correctly and execute it. Either Alt-F8 from within Excel and choosing this macro, either an F5 if you are in VBA and the cursor is inside the current macro.

 

[Elnicko]

 

[Elnicko]

Ok so basically when I select a sales rep on the master job register I want it to automatically populate certain cells on thats sales reps work sheet.

Is this possible?
Uploaded with ImageShack.us

 

[Elnicko]

And if the cells are fill then I want it to use the next row.
Uploaded with ImageShack.us

 

[Elnicko]

.

 

[wigi]

Hello

Select the dropdown (after changing it) and execute this macro:

Sub Tst()
    
    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

For instance, you could assign a shortcut key to the macro (like Ctrl-q for instance).

 

[Elnicko]

Thanks again wigi,

I'm not sure how to attach this code to a dropdown list tho.

I've got the below code working tho, with the intention of using macros to
pull cell data from rows, f,g,h,k adjacent to the dropdown list, and put them into the next available cell in the relevant work sheet in colums a,e,f.

Does your code work for this? How do I apply it?


I have 1000 cells with dropdown lists in them in colum I, this needs to be applied to all of them. (their all the same)

And If a different value is selected I need it to undo the last Marco.

Is this possible

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
End Sub