Move entire row to another worksheet based on cell value

Try this code

Option Explicit

Sub copy_rows()
Dim lrow As Long, i As Long, lastrow As Long

With Worksheets("Project Pipeline")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    lastrow = Worksheets("Project Tracker").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("F" & i).Value = "TBD" Or .Range("F" & i).Value = "N" Or .Range("F" & i).Value = "C" Or .Range("F" & i).Value = "H" Then
            .Range("A" & i & ":D" & i).Copy Worksheets("Project Tracker").Range("A" & lastrow + 1)
            .Range("F" & i).Copy Worksheets("Project Tracker").Range("E" & lastrow + 1)
            .Range("G" & i).Copy Worksheets("Project Tracker").Range("F" & lastrow + 1)
        End If
    Next i
End With

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.

I'm a fan of using AutoFilter to move all appropriate rows in a single pass, so this non-looping method is one I would employ. (note, your sample sheet has a lot of hidden data way down the sheet, so clear all that in columns A:G before trying this:

Option Explicit

Sub CopyRows()
Dim LR As Long, BR As Long, NR As Long

With Sheets("Project Pipeline")
    .AutoFilterMode = False
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("I2:I" & LR).FormulaR1C1 = "=OR(OR(LEFT(RC6,1)={""C"",""H"",""N""}, RC6=""TBD""))"
    .Range("I:I").AutoFilter Field:=1
    .Range("I:I").AutoFilter Field:=1, Criteria1:=True
    BR = .Range("A" & .Rows.Count).End(xlUp).Row
    If BR > 1 Then
        NR = Sheets("Project Tracker").Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A2:D" & BR & ",F2:G" & BR).Copy Sheets("Project Tracker").Range("A" & NR)
    End If
    .AutoFilterMode = False
    .Range("I:I").ClearContents
End With

End Sub

Jerry-

Thanks that worked awsome. In my acutal spreadsheet i need it to return Column (B,C,D,E,N,O) and its now returning (a,b,c,d,e,f)

see the modifications i did. can you help me return what i need when i change the range it it gives me a 400 error

Sub CopyRows()
Dim LR As Long, BR As Long, NR As Long

With Sheets("Project Pipeline")
    .AutoFilterMode = False
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("I2:I" & LR).FormulaR1C1 = "=OR(OR(LEFT(RC14,1)={""C"",""H"",""N""}, RC14=""TBD""))"
    .Range("I:I").AutoFilter Field:=1
    .Range("I:I").AutoFilter Field:=1, Criteria1:=True
    BR = .Range("A" & .Rows.Count).End(xlUp).Row
    If BR > 1 Then
        NR = Sheets("Project Tracker").Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A2:D" & BR & ",F2:G" & BR).Copy Sheets("Project Tracker").Range("A" & NR)
    End If
    .AutoFilterMode = False
    .Range("I:I").ClearContents
End With

End Sub

This code works but how do i make it override the info it reported last time when I run/refresh the macro?

I want it to auto update and that add infoto the bottom when the other rows met criteria

Can anyone help with this

See code below

Sub CopyRows()
Dim LR As Long, BR As Long, NR As Long

With Sheets("Project Pipeline")
.AutoFilterMode = False
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("I2:I" & LR).FormulaR1C1 = "=OR(OR(LEFT(RC14,1)={""C"",""H"",""N""}, RC14=""TBD""))"
.Range("I:I").AutoFilter Field:=1
.Range("I:I").AutoFilter Field:=1, Criteria1:=True
BR = .Range("A" & .Rows.Count).End(xlUp).Row
If BR > 1 Then
NR = Sheets("Project Tracker").Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("B2:E" & BR & ",M2:N" & BR).Copy Sheets("Project Tracker").Range("A" & NR)
End If
.AutoFilterMode = False
.Range("I:I").ClearContents
End With

End Sub

As per forum rules, please EDIT your posts above and add CODE and /CODE tags around the VBA as explained in my signature. Lots of useful tips in the FORUM RULES, read the link above.

Thought i had already done so.

Here it is

Sub CopyRows()
Dim LR As Long, BR As Long, NR As Long

With Sheets("Project Pipeline")
.AutoFilterMode = False
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("I2:I" & LR).FormulaR1C1 = "=OR(OR(LEFT(RC14,1)={""C"",""H"",""N""}, RC14=""TBD""))"
.Range("I:I").AutoFilter Field:=1
.Range("I:I").AutoFilter Field:=1, Criteria1:=True
BR = .Range("A" & .Rows.Count).End(xlUp).Row
If BR > 1 Then
NR = Sheets("Project Tracker").Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("B2:E" & BR & ",M2:N" & BR).Copy Sheets("Project Tracker").Range("A" & NR)
End If
.AutoFilterMode = False
.Range("I:I").ClearContents
End With

End Sub

Maybe like so:

Option Explicit

Sub CopyRows()
Dim LR As Long, BR As Long

With Sheets("Project Pipeline")
    .AutoFilterMode = False
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("I2:I" & LR).FormulaR1C1 = "=OR(OR(LEFT(RC14,1)={""C"",""H"",""N""}, RC14=""TBD""))"
    .Range("I:I").AutoFilter Field:=1
    .Range("I:I").AutoFilter Field:=1, Criteria1:=True
    BR = .Range("A" & .Rows.Count).End(xlUp).Row
    If BR > 1 Then
        Sheets("Project Tracker").UsedRange.Offset(1).Clear
        .Range("B2:E" & BR & ",M2:N" & BR).Copy Sheets("Project Tracker").Range("A2")
    End If
    .AutoFilterMode = False
    .Range("I:I").ClearContents
End With

End Sub

Back on post #4 we had "that works awesome" to now... what?

I'm going to have to move on. Sorry if it wasn't awesome enough. I'll ping some others to take a look. Cheers.

If that takes care of your original request, please select Thread Tools from menu above and set this topic to SOLVED.