update existing code to copy and KEEP existing data

OK, Thanks.

This macro does the following:

user inputs data into 2016 reject tab;
macro then reads "part number", copies the input data into the sheet with its corresponding part number.
As I was asked originally for, it delets the data from sheet 2016 reject (the input page), and sets itself ready for the next data input by user.

BUT, now mgmt has asked me to leave all the data input byu the user on sheet 2016 rejects, and also copy data into the individual sheet corresponding to the part number.

I know the last code .ClearContents needs to come out to not delete the data the user input, but my question is how to get the macro to begin coping from the row with the new data in it.

thanksQA Disposition - Rejects 2016 working model.xls

So-- you can tell that I am very new to this coding as well. Although, I am the "go to" here for this stuff. As I am attempting to refine my older macro, and learn as well, I am attempting to figure out what your code is and what it does.

My requirement is to input data on the "2016 rejects" sheet. Have it copy over to a tab for each part number-- this separate sheet will have only that part numbers data, and have a total at the top for graphing purposes. I was asked to have it delete entries as it copied from the "2016 reject" page. I have now been tasked to keep all data on the "2016 rejects" sheet as well as copy it into its own individual page.

My biggest task right now is to figure the code that will read to the last line of data already copied(and totaled) and begin with the new data.

So, my "for" loop would begin with the first new entry. I am thinking that is the variable defined as "lc" in the above code---- am I correct?

Example:

Last week, I had input data up to row 25. That has already been copied and totaled on the individual parts pages.
I put data in beginning on row 26 and end on row 37. My biggest quest is to figure out the code needed to not repeat copying data from row 1-25, and begin on row 26.

Thanks again for all your help. Trying to learn quickly.

Originally Posted by xladept

Hi Scott,

Try this - I think it does the same thing:

Please Login or Register  to view this content.

BTW -Your sample has no data:|

This should have been included with my last post as well.....

OK. I have put in sample data on the 2016 reject page. If you run the macro as is, it will copy the rows, and paste them on their individual prodcut code page. Then delete the data from 2016 rejects.

Originally Posted by xladept

Hi Scott,

Isn't that what you originally wanted? What do you want now?

No Sir. I need to keep the input data on 2016 Rejects, and copy it to the approprate page for totaling. My issue is that when I attempt to do this, I keep rereading data input to the 2016 rejects sheet that was already copied from days before.

Originally Posted by sourabhg98

hey there,
Check attached.

So, you now enter one more row of data for any part and click on "update". This will update the new data and will keep the old one as it is.
Also, if no data is entered and update button is clicked this will display a message.

I am using cell O1 in the rejects sheet as reference to the old line.

PLEASE NOTE THAT DATA YOU DELETE WOULD NOT BE UPDATED AND THIS MIGHT CREATE PROBLEMS FOR THE MACRO.

Please Login or Register  to view this content.

Thanks. I think I get the gist of it. A few questions:

1) Will k need to be defined? And will this search through the 32 different sheets I have to copy the data to the correct one?
2) Would be just as prudent to use if lr1<lr2?

Originally Posted by sourabhg98

1. K here is very much defined.
"k" has a different value for each "target" and target has a different value for each "n". So for every "n", first "target" is calculated which gives us the "sheet name" of the target sheet. Now, "k" based upon the target sheet gives us the row number of the LAST row on the target sheet so that we can put values below the last row.

2. Yes, I must say that would be better to use lr1<lr2 to skip errors. However, errors would only occur if you delete the data on the rejects sheet. So if you ever delete the data from the rejects sheet, then you have to manually delete that line from the sheet of that part number and you need to change Cell O1 to the row number of the last row manually.

OK. GREAT! I have 1 more question. Given the code I had before, will this replace all of that code; or just a portion of it? I knew there was a more code efficient way to write the program rather than using all the case statements, I just couldn't come up with a good example that I could mock and get it to work.

Thanks again

Sourabhg98 Would it be possible and ok with you if I private messaged you? I would like to go through the lines of code so I can understand what you did, and how your code works. I think I get mose of it and the commands. Just a few questions.

Thanks