title: Drop down list of names, choose a name, copy and paste person's rows of information from one sheet to another
body: <p>I'm new to VBA and trying to make a drop down list of names(XXX, YYY, ZZZ, etc.) of people within different jobs, then you can select one name from the list, then have their rows of information for that person from Sheet7 copied and pasted to Sheet3 where the search bar is. I haven't been successful in this and would appreciate some help. I made the code more generic to the names and job for this post. Thanks in advance.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  'On Error GoTo Cleanup
  Application.ScreenUpdating = False: Application.EnableEvents = False
  
  With Sheet3
      For i = .Cells(.Rows.Count, 3).End(xlup).Rows To 2 Step -1
      Select Case .Cells(i, 3).Value = c
          Case "XXX", "YYY", "ZZZ", "AAA", "BBB", "CCC", "DDD", "FFF", "GGG", "HHH", "III", "JJJ", "LLL", "MMM", "NNN"
          .Rows(i).Copy 
          End Select
  With Sheet3
  Dim lastrow As Long
  lastrow = Range("A65536").End(xlUp).Row
  
  Worksheets(i).Activate
  Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
  End With
  End With
  'Cleanup:
  'Application.ScreenUpdating = True: Application.EnableEvents = True
  
  End Sub
  </code></pre>