All,
I'm creating an Action Item List that once column J is changed to "complete", would move the row to a second sheet. I've found on a post that has the solution I need (see hyperlink below) but I don't know how to tweak it so that it uses column J and is extended for a couple of hundred rows.
Any help would be very much appreciated!!
Regards,
ERhttp://www.excelforum.com/excel-prog...lds-value.html
Would you like to move the row each time the column J is marked as 'complete'? or would you want to update column J for all the required rows and then click on a button to trigger the movement?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi Arlette,
I would like to have the row moved when marked complete, but either solution would work.
Eric
The code would differ in each case (button v/s instant) so if you let me know which type, i can assist you.
Instant would be great. Thank you so much for the quick response!!
What is the data range you have? From column A to?
A to J. Here is a snap shot of the headers, if that helps.
Capture.JPG
So your headers are in row 6?
That is correct.
I modified the code in your linked post as below -
Right click on the sheet where you have the data and select "View Code". Copy paste the above code there.
Please Login or Register to view this content.
Let me know if you face any issues.
I get the following error message:
Run-time error '9':
Subscript out of range
User error, perhaps??
Is your "Complete" sheet spelt in the same way, with respect to case and spelling?
Yes, it is spelled the same, with the same upper case C. I choose it from a data validation drop down list that has two options: In Progress / Complete, if that matters.
I am asking you about your sheet name. Is it spelt correctly - as "Complete" ?
Sorry. Not, the sheet is titled Action Items and the second sheet is titled Completed Action Items.
Updated code here -
Please Login or Register to view this content.
Still getting an error message. This time:
Run-time error '13':
Type mismatch
Sorry for the hassle!!
Is this line highlighted?Change it toPlease Login or Register to view this content.Please Login or Register to view this content.
That works wonderfully. Just one issue: it only works on rows that are on the sheet, but not new rows: example - if I add something to row 12 and change J12 to "Complete" it does not move it. Records that are already on the sheet work fine and are moved to the "Completed Action Items" sheet.
Do you have a sample excel file that you can upload?
Yes, please see the attached sample. Let me know if additional information would be of help!
I tested it and it works.
Hmm, maybe I'm doing something wrong. The sheet I uploaded has row J8 and row J14 as "Complete", yet it did not move the row. The sheet is enabled...what am I missing?
It depends on how the data was entered into those 2 rows. I did a test on your whole file uptil row 20 and it worked fine.
Clear out your data and put it afresh into the tab and check.
It works if you clear the rows in sequence, but if you change one of the rows that is not the top row, it will not work.
I got it.
Updated code here -
You had used the same variable LR to check for the lastrow in the source sheet and the destination sheet, hence it was not working for some rows.Please Login or Register to view this content.
Unfortunately, it is not working for me....and now the first row after the header is overwriting the line so the "Completed Items" sheet only has 1 row total.
Which is the column in the main sheet which will always have an entry?
Edit:
Try this
Please Login or Register to view this content.
WE HAVE A WINNER!!!How do I give you a 1000 stars! I'll update to Solved. Thanks a million!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks