Find records that belong to a combo box value

  • Thread starter Thread starter IP
  • Start date Start date
I

IP

I added a combo box to an Excel wks. I need to find all the records on the
same worksheet where column B value=ComboBox value. Then I need to copy the
records and paste into another worksheet.
Is there a way of doing this in Excel?
I appreciate any help. Thank you.
 
If this is not what you are looking for, maybe you can adapt it to do what
you want. The code below goes into the Sheet code module for the sheet with
the combobox. To access the code module, right click the sheet tab and then
click View Code on the pop up menu. Copy the code below and paste it into
the code window that opens. Save the file and you are in business. When
you make a selection in the combobox, it will copy the rows for all matches
to sheet1.

Private Sub ComboBox1_Click()
Dim lr As Long, rng As Range
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh1.Range("B2:B" & lr)
For Each c In rng
If c.Value = Me.ComboBox1.Value Then
c.EntireRow.Copy _
sh2.Range("A" & sh2.Cells(Rows.Count, 2) _
.End(xlUp).Row + 1)
End If
Next
End Sub
 
Back
Top