Forum Discussion

Angela2021's avatar
Angela2021
Copper Contributor
Apr 18, 2021

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

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 ...
excel
Angela2021's avatar
Angela2021
Copper Contributor
Apr 19, 2021
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?
HansVogelaar's avatar
HansVogelaar
MVP
Apr 19, 2021

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

Resources