Apr 18 2021 08:38 AM
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.
Apr 19 2021 09:40 AM
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.
Apr 19 2021 02:07 PM
Apr 19 2021 02:26 PM
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
Apr 19 2021 02:35 PM
Apr 19 2021 02:44 PM
Does it work if you change
If rng.Value = "Dual" Then
to
If LCase(rng.Value) Like "*dual*" Then