PDA

View Full Version : Solved: Copying Rows



j19_2002
09-22-2008, 01:34 PM
I have another problem I need help with. I have attached a spreadsheet showing the layout of work being done. What I want to do is once the entries are complete, I want to check each row and if there's an entry under "Code" I want to take the complete row and have it copy and attach it to the worksheet named 'Notification'. I would like to have it happen for all of the entries in the "Worklist". Could someone please help??

if the worksheet needs to be re-arranged please let me know as well.

Thank You.

xld
09-22-2008, 03:48 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Not Intersect(Target, Me.Columns("N")) Is Nothing Then

LastRow = Worksheets("Notification").Cells(Rows.Count, "A").End(xlUp).Row
Target.EntireRow.Copy Worksheets("Notification").Cells(LastRow + 1, "A")
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Not Intersect(Target, Me.Columns("N")) Is Nothing Then

LastRow = Worksheets("Notification").Cells(Rows.Count, "A").End(xlUp).Row
Target.EntireRow.Copy Worksheets("Notification").Cells(LastRow + 1, "A")
End If
End Sub

j19_2002
09-22-2008, 03:58 PM
am I doing something wrong, when i try and run it, it doesnt recognize anything.......it asks me to run a macro and i cant find anything. Sorry I am a novice at this......

xld
09-22-2008, 04:02 PM
Sorry that messed up a bit, this is what I meant to post



Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Not Intersect(Target, Me.Columns("N")) Is Nothing Then

LastRow = Worksheets("Notification").Cells(Rows.Count, "A").End(xlUp).Row
Target.EntireRow.Copy Worksheets("Notification").Cells(LastRow + 1, "A")
End If
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

j19_2002
09-22-2008, 04:23 PM
ok i went ahead and copied the code into worksheet1 which is where all the information is being inputted. It gave me this error after I completed one row and hit enter after entering the code:

Run Time Error '9':
Subscript out of range

and it highlighted the text below in yellow:

LastRow = Worksheets("Notification").Cells(Rows.Count, "A").End(xlUp).Row

Just FYI: i am putting information into sheet 1 and once a code is entered at the end I want that row to be copied into sheet 2 which is named Notification

xld
09-22-2008, 04:33 PM
That says that you don't have a worksheet called Notification!

j19_2002
09-22-2008, 04:40 PM
LOL!!! I am so sorry. I forget that spelling in programming is so sensitive. I had an I where it shouldnt have been..... =)

it works perfect!!!


Thank You So Much for your help. I'm sure i'll be back next week with another problem.....

j19_2002
09-24-2008, 10:04 AM
It works perfectly but there's one thing I have noticed when entering the error codes, it's very key sensitive. Is it possible to have it only copy after you press enter after entering the code?? because if I enter the code and than press enter it copies the row twice.

xld
09-24-2008, 10:40 AM
It should only work after enter, and not twice.

j19_2002
09-24-2008, 11:21 AM
It does it twice for some reason. If I enter the code and than click on another cell it will do it only once but it's a habit to press enter and to go back and check the 2nd sheet defeats the purpose of having the macro. It's weird I know, cuz I looked at the code and it's only suppose to happen when you hit enter like you say, but I duuno, maybe I did something wrong?? Also is it possible to have it auto-update itself. Lets say for example I made an error on the code part and go back and change it to another code, instead of copying it again and make it a 2nd entry, can it just auto-update the code and leave the rest of the row the same? It's not necessary, just asking if possible =]