Forum Discussion
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 ...
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 SubSwitch 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?
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?
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