| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
I'm not sure why you used .range("A" & 5 + 1). That will always be
..range("A6"). And that means that row 6 will always get the info pasted into it. This portion With Sheets("CODE ERROR") lr2 = .Cells(.Rows.Count, 6).End(xlUp).Row End With Bases the lastrow on what's in column 6 (F) of the sheet named "Code Error". If that column isn't always filled in with something, then your code may not be finding the lastrow correctly. If your data looks like: A1 ... F1 ... A2 ... F2 ... A3 ... ... A4 ... ... The lr2 variable will be row 2--even though you have data in rows 1:4. You'll have to find a column that always has something in it to determine that lastrow (LR2 variable). But then you'd want: Targer.EntireRow.Copy Sheets("CODE ERROR").Range("A" & lr2 + 1) So if the lastrow used (based on column F) was 1234, then lr2+1 = 1235 and that's the row that would get the line pasted. 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 -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
P.S. Be sure to delete the other code or it will cause errors.
"OfficeMan" wrote: 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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COPYING ONE ROW TO ANOTHER SHEET AFTER CELL INPUT
Change this from:
Target.EntireRow.Copy Sheets("CODE ERROR").Range("A" & lr2) To: Target.EntireRow.Copy Sheets("CODE ERROR").Range("A" & lr2 + 1) To prevent overwriting the last entry. "JLGWhiz" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I copying data from a cell on sheet to a diff cell/sheet | Excel Worksheet Functions | |||
| copying input to another cell | Excel Worksheet Functions | |||
| checking input in a cell and return by copying info from a other c | Excel Worksheet Functions | |||
| Copying values from one sheet to the same cell in another sheet | Excel Discussion (Misc queries) | |||
| Copying cell with input range to different worksheet | Excel Worksheet Functions | |||
Linear Mode
