VBA to match cell and copy row

[MNFPGE]

Hi, I'm looking for a VBA to find a matching identifier and copy a row to another sheet. In Sheet 1 I have a drop down list of options and I want the user to be able to choose an option from that list. The option they choose has a matching row of data on Sheet 2, this data should be copied and pasted into Row 2 on Sheet 3. The data in Sheet 2 has the same options in column C to identify which row of data should be copied.
Below is Sheet 2 with the identifiers in Column C which are identical to the drop down list to choose from.

 

[B4andafter]

Hi, can you be more specific. For example, what option do you want the user to choose from, is it the items in Column C in Sheet two, the only option list? Not sure here, then we can start the process.

 

[MNFPGE]

Hi, can you be more specific. For example, what option do you want the user to choose from, is it the items in Column C in Sheet two, the only option list? Not sure here, then we can start the process.

Hi, Sorry about the delay. Yes the only options available will be those listed in Column C and the will choose these from a drop down list. Thanks very much!

 

[B4andafter]

Got it, really busy this week...I will have something by this weekend for you to test out....thanks

 

[B4andafter]

Alright here go, first as you know, you will have to setup a Userform with Combobox with the dropdown list from your sheet two column C. I recommend selecting the this column with the identifiers and naming this range. You can search this forum on how to add said Userform ...in addition also recommend this link for adding Userform (there's multiple options for you to use as well)

Here is the code that you will need to add to the respective buttons Ok and Close (my example here I use Ok and Cancel on the user form below the dropdown box)

Private Sub CommandButton1_Click()

End Sub

Private Sub cmdClose_Click()          'this is the code to the Close button
    Unload Me
     
End Sub

Private Sub cmdOK_Click()              'this is the code to the Ok button after a selection is made

Dim sh2 As Worksheet, sh3 As Worksheet, f As Range


If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub     'the ComboBox1.Value is the item selected

Application.ScreenUpdating = False


  Set sh2 = Sheets("Sheet2")
  Set sh3 = Sheets("Sheet3")
    
  Set f = sh2.Range("C:C").Find(ComboBox1.Value, , xlValues, xlWhole)

  If Not f Is Nothing Then
      
    sh2.Range("C" & f.Row & ":N" & f.Row).Copy sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)      [SIZE=3]'I used the last column N based on the screen shot you sent-change as needed[/SIZE]
        
        
  Else
    MsgBox "Does not exist"
    
  End If
  
  MsgBox "Please select another item, else exit Userform"
   
  
End Sub



Private Sub UserForm_Click()

End Sub

Hope this helps, the codes work on my side.

 

[MNFPGE]

Thanks so much I'll put this code into my sheet and see how I go. Your help is very much appreciated!