Lookup dropdown list value in a another sheet and copy row accordingly [Solved]

Report
-
Posts
2745
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 17, 2021
-
Hello,

I am trying to find out if I can create a drop-down list in Sheet1 that when I make my selection, it will copy part of a row from Sheet2 onto Sheet1 next to my selection?

2 replies

Posts
2745
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 17, 2021
461
Hi Torrey,

Could you provide some more details?
Do you know how to create a dropdown list?
When you select a value from the dropdown list, how do we know which part of sheet2 needs to be copied?

Best regards,
Trowa

I do know how to create a dropdown list.

My dropdown is in column A from sheet2. I want the data from that row, that is stored from Column B thru Column AS to be copied to sheet1 onto the same row as the dropdown cell, going from the dropdown cell to the right.

Basically, I am trying to create a bid spreadsheet that has multiple calculators that I have already created and stored one calculator per row in sheet2. Column A of sheet2 is the name of the calculator.

When I create a bid on sheet1, I want to be able to select a calculator (Row) from the dropdown list and have only that row from Column B to AS copied into my bid sheet.


Thank you for helping me with this.


Torrey
Posts
2745
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 17, 2021
461
Hi Torrey,

The code below will look up the value from the dropdown list in column A of Sheet1 in column A of Sheet2. If a match is found, it will copy the row of column B to AS of Sheet2 to column B of Sheet1.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Columns("A"), Target) Is Nothing Then Exit Sub
Dim mFind As Range
With Sheets("Sheet2")
    Set mFind = .Columns("A").Find(Target.Value)
    If mFind Is Nothing Then Exit Sub
    .Range(.Cells(mFind.Row, "B"), .Cells(mFind.Row, "AS")).Copy Target.Offset(0, 1)
End With
End Sub


To implement the code, right-click Sheet1's tab and select 'View code', then paste the code in the big white field. The code will run automatically when you select a value from one of your dropdown lists in column A of Sheet1.

The name of Sheet1 (or your bid sheet) doesn't matter, but make sure your second sheet is named Sheet2. Or replace Sheet2 in the code with your sheet name.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!