Search table, copy rown and paste into new sheet

[Daniel]

Hi all!

I am a newbie to VBA and I have tried to do the following but not succeeded:

I have a table (as seen in the example file attached). I want to look up a value in column b, for instance NOR-046-MKC, fed in by a user and copy all rows with that value in column b and paste them into a new worksheet in the same workbook.

I am guessing it is simple and I have tried to search the forum (which is by the way great) but have not been able to solve my problem.

Any help is greatly appreciated.

Daniel

[mdmackillop]

Hi Daniel,
Welcome to VBAX

Option Explicit
Sub MoveText()
Dim ID As String, FirstAddress As String
Dim Rng As Range, c As Range
ID = "NOR-" & InputBox("Enter number", , "046") & "-MKC"
With Sheets("Sheet1").Columns(2)
Set c = .Find(ID, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If Rng Is Nothing Then
Set Rng = c
Else
Set Rng = Union(Rng, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Rng.EntireRow.Copy Sheets.Add.Range("A2")
End Sub

[lucas]

I came up with this to do the same Malcolm....

[mdmackillop]

Neat Steve, but if your using filters, how about using it for the combobox too.
Private Sub UserForm_Initialize()

'Filter unique values
Range(Cells(4, 2), Cells(Rows.Count, 2).End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("IV1"), Unique:=True
'Add to combo
ComboBox1.List() = Range(Range("IV2"), Range("IV2").End(xlDown)).Value
'Delete list
Range(Range("IV1"), Range("IV1").End(xlDown)).ClearContents
End Sub

[lucas]

Good idea...less code.
I was just trying to get a unique list in the combobox.

Any advantage to the filter method to populate the combo? Since you have to copy to a range and then delete them....what if you didn't have the space to do this(like that's going to happen)....just curious.

[mdmackillop]

Down to speed really. Looping will serve small ranges, the filters will be quicker on large ranges. I don't know if this can be done within the code. Interesting problem.

[lucas]

I kept both for future reference...seems like it might be useful...along with your notes on speed, and size of ranges. Thanks for the input.

[Daniel]

Wow! Thank you for the response! Great help! I have loads to learn apparently.

/Daniel

[mdmackillop]

Hi Daniel
You have, and it never ends.:yes
Regards
MD