Copy and paste rows that contain certain text to a seperate tab VBA beginner

[VBAlearner1]

Hi

I'd appreciate some help. I know excel quite well but I'm new to VBA.

I'd like a code that copy and pastes complete rows from sheet 1 to sheet 2, however I only want to copy and paste the rows that have certain text (the number 1) in column G.

Can anyone help please?

 

[mrjimmyvo]

Another approach ... put the following code into the code module for your worksheet (not into a regular module).\

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    If Target = "1" Then
        Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

Love the code!

Can you please help me out and edit the code to filter out certain characters in the cell?

Like if it already contains a 1 in any part, it will copy the row to the next sheet?

MUCH appreciated!

 

[mumps]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    If Target Like "*1*" Then
        Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

 

[mrjimmyvo]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    If Target Like "*1*" Then
        Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

That works!! Now all I need is to copy the row above the one with the target text... If you can can help me with that pleaseeeee.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    If Target Like "*1*" Then
        Cells(Target.Row - 1, 1).EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub