Code to copy variable data to new worksheet

[Pipski121]

Hi,

I hope someone can help me. I can't write code but I copy and paste useful stuff that other people have kindly written and posted on the internet. I think the problem I have will be a pretty easy one to solve for someone with a bit of knowledge.

What I want to do is search a column for a value and then copy the entire row into a new worksheet. I want to be able to search using an input box as the values will be variable. The cells in the column will contain other data, as well as the searched value.

I have found a code on the internet that nearly does what I want but it looks for an exact match of the value in the cell and then copies the row. What I want to do is edit this code so it will copy the row if it contains the searched value, instead of looking for an exact match.

I've pasted the code I've found below - hope someone can help, thanks.

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:

 

[VoG]

Not the best code in the world but try replacing

If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

with

If Range("E" & CStr(LSearchRow)).Value Like "*" & LSearchValue & "*" Then

 

[Pipski121]

Your solution worked a treat, many thanks. Sadly I'm oblivious to good and bad code, but just grateful for anything that works!

 

[Pipski121]

As a foot note - if anyone can come up with a better code I'd happily use it.