Copy row from Main data sheet to to a different sheet based on value input

[MilkyWay1]

Hi, very new to intermediate excel skills. I've been browsing this site over several days but still cannot get the following to work:

-I have a main data sheet named "Main" which has a header from A-P and about 150 rows of data
-I want to copy a row if the input of Column 'O' is set to "Active"
-Entry to column O is based on dropdown from a list in a sperate sheet. One of the options in the list is obviously "Active"
-If Active is selected, I'd like to copy that row to sheet "Active Items"
-However, I only want to copy columns A-H from sheet "Main" to sheet "Active Items"
-Lastly, the sheet "Active Items" has a header identical to "Main" but only columns A-H which I want copied. Other than that, the sheet is empty.

I was playing with the following code which I got from a different thread. I actually successfully got it to work at one point but cannot replicate my one successful row copy. Can anyone help me understand what I'm doing wrong? I am copying the code to sheet "Main"

 

[earthworm]

You need to use loop with condition If along with cell reference to check each row individually and then do the stuff onwards if the value is true otherwise move to next .

 

[MilkyWay1]

You need to use loop with condition If along with cell reference to check each row individually and then do the stuff onwards if the value is true otherwise move to next .

I'm afraid I'm not savvy enough implement this code without an example.

I found this other VBA code (see below) from another thread which worked, but every time it copies to the new sheet, it writes over the existing copied row. So there is only one row in the copied sheet (the latest row copied in) as opposed to a list of all rows copied in....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E:E")) Is Nothing Then
If Evaluate("isref('" & Target.Value & "'!A1)") Then
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
MsgBox "No sheet exists for " & Target.Value
End If
End If
End Sub

 

[earthworm]

I

I'm afraid I'm not savvy enough implement this code without an example.

I found this other VBA code (see below) from another thread which worked, but every time it copies to the new sheet, it writes over the existing copied row. So there is only one row in the copied sheet (the latest row copied in) as opposed to a list of all rows copied in....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E:E")) Is Nothing Then
If Evaluate("isref('" & Target.Value & "'!A1)") Then
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
MsgBox "No sheet exists for " & Target.Value
End If
End If
End Sub

try to make changes in above bold highlighted part.

 

[Fluff]

try to make changes in above bold highlighted part.

It would help if you told the OP what to change.