Insert data to table list reference drop down list

[PostTIT]

I'm wanting to input data to a cell then run a macro that will transfer that data to a table elsewhere on the sheet.
Which list in the table it gets added to is dependant a drop down list that is selected.

That's the jist of it at least... Onwards!

Cell C2 contains the drop down list that has three options 'Activity List', 'People', and 'Status List'.
Cell D2 is the text string that will be entered by the user.
Then a button gets pressed which runs the macro.
The data in D2 is placed at the end of the correct table.
There are three tables: 'Activity_List', 'People', and 'Status_List', starting at H7, I7, and J7 respectively.

So I would like the macro to notice the text in C2, locate the corresponding table, and take the data from D2 and place it at the end of the table.

Any help would be very much appreciated.

 

[Akuini]

I'm wanting to input data to a cell then run a macro that will transfer that data to a table elsewhere on the sheet.
Which list in the table it gets added to is dependant a drop down list that is selected.

That's the jist of it at least... Onwards!

Cell C2 contains the drop down list that has three options 'Activity List', 'People', and 'Status List'.
Cell D2 is the text string that will be entered by the user.
Then a button gets pressed which runs the macro.
The data in D2 is placed at the end of the correct table.
There are three tables: 'Activity_List', 'People', and 'Status_List', starting at H7, I7, and J7 respectively.

So I would like the macro to notice the text in C2, locate the corresponding table, and take the data from D2 and place it at the end of the table.

Any help would be very much appreciated.

There are some inconsitencies, for example:
One of the table name is 'Activity_List', but it is 'Activity List' (without underscore) in dropdownlist.
So, change the dropdown list to make it consistent with all the table names
Then try this code:

Sub a1010515a()
Dim r As Range
Set r = ActiveSheet.ListObjects(Range("C2").text).Range.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
r.Offset(1, 0) = Range("D2").Value

End Sub

 

[PostTIT]

I feel like you picked up something that wasn't really relevant... Not to say you aren't right, it just wasn't relevant...

I solved my issue by recording my macro and fiddling around:

Sub test2()
'
' test2 Macro
'
Dim X As String, Y As String


X = Range("C2").Value
Y = Range("D2").Value


If X = "" Or Y = "" Then
    MsgBox "you tried and failed"
    Exit Sub
End If
    
If Range("C2").Value = Range("H7").Value Then


    Range("D2").Select
    Selection.Copy
    Range("H7").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Range("H7").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell = Range("D2").Value
                
    Else
    End If
    
If Range("C2").Value = Range("I7").Value Then


    Range("D2").Select
    Selection.Copy
    Range("People[People]").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Range("People[[#Headers],[People]]").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell = Range("D2").Value
                
    Else
    End If
    
If Range("C2").Value = Range("J7").Value Then


    Range("D2").Select
    Selection.Copy
    Range("J7").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Range("J7").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell = Range("D2").Value
                
    Else
    End If
    
Range("D2").ClearContents


    
End Sub

So it works, where C2 is the cell that corresponds to the header of the table, and D2 gets placed into a new row in that table.

If anyone has any recommendations as to how to make it more concise/simplify or how to make it more robust I'd love to hear how. Thanks all.