Extracting data using a Dropdown list that will auto generate in another sheet.

Occasional Contributor

I am very new to excel so excuse my lack of knowledge. I am trying to get information in a row to auto populate in another sheet when I select an item from a dropdown list. So, if I choose "dual" in F2 (sheet1), I would like the date (A2), name (B2) and age (C2) auto populate in sheet 2 the date (A2), name (B2) and age (C2).

 

Is this possible? If so, how can I accomplish this. I tried to screen shot but was unable to.

 
 

 

 

 

 

 

 

5 Replies

@Angela2021 

Right-click the sheet tab of the first sheet.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim wsh As Worksheet
    If Not Intersect(Range("F:F"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set wsh = Worksheets("DUAl SEARCHES")
        r = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Intersect(Range("F:F"), Target)
            If rng.Value = "Dual" Then
                r = r + 1
                wsh.Range("A" & r).Resize(1, 3).Value = Range("A" & rng.Row).Resize(1, 3).Value
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook.

Make sure that you allow macros when you open it.

 

I have attached a version with the code.

Thank you so much!
The one you created works like a charm. When I try to recreate it it doesn't work. The only difference I can see is that the "DUAL" in my other worksheet is a hyperlink. Not sure if this makes a difference. Would I be able to use this code for the shelter drop down in the same row along with the dual for the shelter tab? Would I just need to past the code under the first one and change the language to "SHELTER" instead?

@Angela2021 

Not sure what you mean by "The only difference I can see is that the "DUAL" in my other worksheet is a hyperlink."

To accommodate SHELTER too, the code could look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim wsh As Worksheet
    If Not Intersect(Range("F:F"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set wsh = Worksheets("DUAl SEARCHES")
        r = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Intersect(Range("F:F"), Target)
            If rng.Value = "Dual" Then
                r = r + 1
                wsh.Range("A" & r).Resize(1, 3).Value = Range("A" & rng.Row).Resize(1, 3).Value
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
    If Not Intersect(Range("H:H"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set wsh = Worksheets("SHELTER LOG")
        r = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Intersect(Range("H:H"), Target)
            If rng.Value = "SHELTER" Then
                r = r + 1
                wsh.Range("A" & r).Resize(1, 3).Value = Range("A" & rng.Row).Resize(1, 3).Value
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
I was looking at the code and in the other workbook I am working on in the "DUAL" I made it a hyper link. On the one you worked on the "dual" drop down is not a hyperlink. I was think this as the reason it was not working for me.

@Angela2021 

Does it work if you change

If rng.Value = "Dual" Then

to

If LCase(rng.Value) Like "*dual*" Then