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?

 

[Andrew Poulsom]

Welcome to MrExcel.

AutoFilter your data for column G equals 1 and copy/paste the visible cells. The macro recorder should give you some VBA code that you can adapt. Hint: use SpecialCells(xlCellTypeVisible).

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("G" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("G" & i).Value = 1 Then .Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
End Sub

 

[mumps]

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

 

[VBAlearner1]

Thank you, I'm sorry i'm at a loss as to how to use the macro recorder to do this and give me VBA code?

 

[VBAlearner1]

Thank you to all...

Vog am i putting this code into the sheet code? How does this code run? Sorry I'm new to VBA.

umps could you explain how I get to where your describing please?

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu then paste in the code. Press ALT + Q.

Press ALT + F8 then double click test

 

[mumps]

Right click the tab that contains your data and then click 'View Code'. Paste the code into the winidow that appears and then close the window. Each time you enter 1 in column G. the row will be copied to Sheet2.

 

[VBAlearner1]

Thank you all so much for your help, mumps that works brilliant!

 

[mumps]

My pleasure.