Macros - Trying to search column B for text "OE", if "OE" is found I need to copy entire row to sheet 2 of the same workbook. However nothing copies o

[Manda1985]

Hi There,

I am a real amateur regarding macros and am trying to search column B in sheet 1 of a workbook for the text "OE". If "OE" is in column B i need the entire row copied to sheet 2 of the same workbook.
I searched and found a code which I thought was for the same thing and just changed the column i want to search and the text to search for (code at bottom of message). However, when i press the click button it says all matching data has been copied but nothing copies to sheet 2. Is there something else in the code i need to change or am I on the completely wrong track?
Please note the original code i found started with "Sub SearchForString ()" instead of "Private Sub CommandButton1_Click()" as used, but nothing would happen when i pressed the button with the "Sub SearchForString ()" could this be a reason it is not working?

Many thanks in advance for your help

Here is a screen shot of how my sheet 1 is set out if that helps in anyway.

Date	Account Title	Comments	Debit $	Credit $
17/12/2015	Bank		670.00
	OE	Funds Intro		670.00
19/12/2015	Bank		460.00
	GST collected			60.00
	Inspection Income			400.00
21/12/2015	Expense - Insurance	Indemnity and Liability	581.19
	GST paid		87.18
	Bank			668.37
22/12/2015	Bank		460.00
	GST collected			60.00
	Inspection Income			400.00

<tbody>
</tbody>

Code currently used


Private Sub CommandButton1_Click()


Dim LSearchRow As Integer
Dim LCopyToRow As Integer


On Error GoTo Err_Execute


'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 B = "OE", copy entire row to Sheet2
If Range("B" & CStr(LSearchRow)).Value = "OE" 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:
MsgBox "An error occurred."


End Sub

 

[Michael M]

Maybe this instead.
And could you use code tags when posting code please.....it's much easier to read AND debug....see my sig block

Private Sub CommandButton1_Click()
Dim lr As Long, LCopyToRow As Integer, r As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
For r = 4 To lr
If Range("B" & r).Value = "OE" Then
    Rows(r).Copy Sheets("Sheet2").Rows(LCopyToRow)
    'Move counter to next row
    LCopyToRow = LCopyToRow + 1
End If
Next r
MsgBox "All matching data has been copied."
End Sub