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.

This will go in the Worksheet code module for Sheets("ERRORS"). Right click
the sheet name tab and then select view code to open the code module window.
Paste this code into the module. This is untested. Post back if any
difficulties in running.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr 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

It should copy the row if you enter "code error" in column G of
Sheets("ERRORS").





"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.
>

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.
>

Thanks a lot for the help!

I have implemented this on my Sheet however I now get a code error of 438
Object does not support this property or method and then it highlights this
line:

lr2 = Sheets("CODE ERROR")(Rows.Count, 1).End(x1UP).Row

Sheet 1 is ERROR and the info starts on row 6
Sheet 2 is CODE ERROR and the info starts on row 6 as well.

Thanks again.


"JLGWhiz" wrote:

> This will go in the Worksheet code module for Sheets("ERRORS"). Right click
> the sheet name tab and then select view code to open the code module window.
> Paste this code into the module. This is untested. Post back if any
> difficulties in running.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr 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
>
> It should copy the row if you enter "code error" in column G of
> Sheets("ERRORS").
>
>
>
>
>
> "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.
> >

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.
> >

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

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
>

I still left a bad code line in there after correcting it in my code module,
I sent you the one from Note Pad. Oh well! It's one of those days.

Your current problem: The way your original posting read was to look for
the words "Code Error" in column G. Apparently you want it to react to any
change in column G. If that is true, then change the following:

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

To:

If Not Intersect(Target, Range("G6:G" & lr)) Is Nothing Then
Target.EntireRow.copy Sheets("CODE ERROR").Range("A" & lr2)
End If

As long as there is data in the target cell of column G, it will run the
macro.



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

Still Not working, sorry...

I apologize for the wrong description –

This is what I want.

I have a form created to input data on a sheet.

The form inputs Column A through G

However the entry on Column G will be different, I would like to copy each
entry to a new sheet labeled according to the input.

Active Sheet is name ERROR
Sheet # 2 is labeled CODE ERROR

Each time the Active Sheet “ERROR” gets an input on Column G
I would like to entire row to be copied to the second sheet if and only if
the input to Column G is “Code Error”

I plan to add more sheet later on but I figure I would just add the names of
the errors later on.

Thank you again.


"JLGWhiz" wrote:

> I still left a bad code line in there after correcting it in my code module,
> I sent you the one from Note Pad. Oh well! It's one of those days.
>
> Your current problem: The way your original posting read was to look for
> the words "Code Error" in column G. Apparently you want it to react to any
> change in column G. If that is true, then change the following:
>
> 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
>
> To:
>
> If Not Intersect(Target, Range("G6:G" & lr)) Is Nothing Then
> Target.EntireRow.copy Sheets("CODE ERROR").Range("A" & lr2)
> End If
>
> As long as there is data in the target cell of column G, it will run the
> macro.
>
>
>
> "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
> > >

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