VBA copy/paste cells based on dropdown selection

Mmcq16

New Member
Joined
Apr 11, 2013
Messages
9
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
What is the cell location of your drop down list in sheet 1?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
Place the following code into the code module for sheet 1 (not into a regular module).
Code:
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

New Member
Joined
Apr 11, 2013
Messages
9

ADVERTISEMENT

Thanks! That worked perfectly!
 

Mmcq16

New Member
Joined
Apr 11, 2013
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
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?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
Try putting this line:
Code:
On Error Resume Next
below this line:
Code:
For x = bottomC To 2 Step -1
Please let me know if that works.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top