I think we are having a communications breakdown. Here is what the code
below does.
1. Sets the last row with data in column G of ActiveSheet to a variable so
that adding and deleting rows will not affect the validity of the range.
2. Sets the last row with data in column A of Sheets("CODE ERROR") to a
variable so that the copied rows will not overwrite as they are copied over
from the other sheet.
3. Tests the value in column G to make sure it is not empty.
4. If column G is not empty then it tests the value of the target cell for
the value "Code Error" by putting everything to lower case. This method
allows for users entering radom upper or lower case letters by (in code only)
converting whatever is there to lower case and comparing it to lower case.
It does not change the cell content.
5. If it finds the value "Code Error", it copies the entire row of data to
Sheets("CODE ERROR") and ends the procedure.
If that is what you want, then copy this cleaned up version of what I
previouly gave you and paste it into the sheet code module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr2 As Long
lr = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
lr2 = Sheets("CODE ERROR").Cells(Rows.Count, 1).End(xlUp).Row
If Not Intersect(Target, Range("G6:G" & lr)) Is Nothing Then
If LCase(Target) = "code error" Then
Target.EntireRow.Copy Sheets("CODE ERROR").Range("A" & lr2)
End If
End If
End Sub
I tested this by creating a user form with a text box. I entered the words
"Code Error" in the text box then executed a macro to paste the textbox value
to a cell in column G of the active sheet with the Change event code. The
text was pasted into the cell and the row that the cell was in was
immediately copied to the second sheet on the next available row. I hope
this is what you are after.
OfficeMan said:
Thanks Dave, this time it did work.
All i did was replace:
Targer.EntireRow.Copy Sheets("CODE ERROR").Range("A" & 5 + 1)
however it now only copies to row 6? - each time I enter a new row on the
Active sheet it does not copied to the next row on the CODE ERROR sheet.