VBA copy/paste cells based on dropdown selection

[Mmcq16]

Hello

I have two sheets that I am using. sheet 2 has a list of experiments in column C with information regarding the experiment in columns F - J. On sheet 1 I have a dropdown box with a list of all the experiments. When I select an experiment from the dropdown box I would like to populate columns K - O on sheet 1 with the information from columns F - J for the selected experiment on sheet 2. Is this possible?. Any help would be apprecicated

-
Mike

 

[mumps]

What is the cell location of your drop down list in sheet 1?

 

[Mmcq16]

It is in multiple rows in column D

 

[mumps]

Place the following code into the code module for sheet 1 (not into a regular module).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim bottomC As Long
    bottomC = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row
    Dim x As Long
    For x = bottomC To 2 Step -1
        If Sheets("sheet2").Cells(x, 3) = Target Then
            Sheets("Sheet2").Cells(x, 6).Resize(, 5).Copy Cells(Target.Row, 11)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Mmcq16]

Thanks! That worked perfectly!

 

[mumps]

My pleasure.

 

[Mmcq16]

Hi, I have a quick followup question. If I would want to type a new experiment in the dropdown box and fill in new data manually, only on sheet 1, how would I amend the code to allow this without coming up with a runtime type mismatch error?

 

[mumps]

Hi Mike. Do you mean that you want to add a new experiment in the drop down list but that experiment does not exist in column C of sheet 2?

 

[Mmcq16]

yes. exaclty

 

[mumps]

Try putting this line:

On Error Resume Next

below this line:

For x = bottomC To 2 Step -1

Please let me know if that works.