copy and paste according to cell value

[saman110 via OfficeKB.com]

I'm looking for a macro that whenever I type "y" in "O" column it copy
corresponding cells from B:N to sheet 2 starting from B:5 and look for
another "y" in col. O and paste it below what it pasted before in sheet 2.

thx.

 

[saman110 via OfficeKB.com]

I found this macro, but the problem is that it copies duplicates. for example
if I type "y" in column O5 and run the macro and then type "y" in O6 it will
copy previous row which is O5 again.

Any idea how to make it not to copy the previous row again?

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim destCell As Range

With Worksheets("Daily")
Set myRng = .Range("O5:O92")
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = "y" Then
With Worksheets("Funnel")

Set destCell = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)

End With
Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Copy _
Destination:=destCell
End If
Next myCell
End Sub

 

[Dave Peterson]

How about changing that Y to some other character after it's copied to the other
sheet.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim destCell As Range

With Worksheets("Daily")
Set myRng = .Range("O5:O92")
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = "y" Then
With Worksheets("Funnel")
Set destCell = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
End With
myrng.parent.Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Copy _
Destination:=destCell
mycell.value = "Copied" 'or just C????
End If
Next myCell
End Sub