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" wrote:

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.

"Dave Peterson" wrote:

Why do you use A6 in this line?
Target.EntireRow.copy Sheets("CODE ERROR").Range("A6" & lr2)

I would think:
Target.EntireRow.copy Sheets("CODE ERROR").Range("A" & lr2)

And you're copying over the last used row. You may want:

Target.EntireRow.copy Sheets("CODE ERROR").Range("A" & lr2 + 1)

ps.

I don't understand what lr is and why it's there?

pps.
Is the event firing?

Add
msgbox "fired"
Near the top of the procedure. It may help you diagnose the problem.

OfficeMan wrote:

Thanks, for some reason it would still not copy the row to the sheet. No
errors - it just won't copy.

This is what i have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr2 As Long
With Sheets("CODE ERROR")
lr = ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Row
lr2 = .Cells(.Rows.Count, 6).End(xlUp).Row
End With
If Not Intersect(Target, Range("G6:G" & lr)) Is Nothing Then
If LCase(Target.Value) = LCase("Code Error") Then
Target.EntireRow.copy Sheets("CODE ERROR").Range("A6" & lr2)
End If
End If



End Sub

"Dave Peterson" wrote:

You're comparing a lower case string to a mixed case string with this line:
If LCase(Target) = "Code Error" Then
It'll never be true.

I'd use:
If LCase(Target.value) = lcase("Code Error") Then



OfficeMan wrote:

Thank you for the help, however I have this following code and no error
message but nothing happens once I input on column G

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr2 As Long
lr = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
With Sheets("CODE ERROR")
lr2 = .Cells(.Rows.Count, 6).End(xlUp).Row
End With
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

"Dave Peterson" wrote:

with sheets("Code error")
lr2 = .cells(.Rows.Count, 6).End(xlUp).Row
End with

OfficeMan wrote:

Thanks -

On this one it's telling me a differnt error

lr2 = Sheets("CODE ERROR").Rows.Count, 6).End(xlUp).Row

On this line it says "Expected end of statement"

"JLGWhiz" wrote:

This one is tested and gets rid of the typos and omissions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr2 As Long
lr = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
lr2 = Sheets("CODE ERROR").Rows.Count, 1).End(xlUp).Row
If Not Intersect(Target, Range("G2: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

"OfficeMan" wrote:

Good Morning!

I have been trying to do this but unfortunately I am not that great with
Macros

I have sheet named ERRORS and on this sheet I have Column A:G
I have sheet name CODE ERROR and on this sheet I have Column A:G just like
the errors sheet.

I want to copy the entire ROW from sheet ERRORS to CODE ERROR each time
Column G on Sheet ERRORS has input "Code Error"

Any help would be appreciated.

Thank you.


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson