+ Reply to Thread
Results 1 to 21 of 21

update existing code to copy and KEEP existing data

  1. #1
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    update existing code to copy and KEEP existing data

    Hello all-
    I have been asked to update my code that I am using to now do the following:

    Read data from the tab called 2016 rejects. copy the contents into an existing page that will tabulate the totals from that specific page, AND NOW keep the original data on the input page (called 2016 Rejects).

    Thanks in advance for your assistance.

    I know that the .clearcontents command will need to be taken off each to leave original input data.

    BUT, what code will need to be added, changed, to read the newly input data, and copy it --- NOT the data from before? I have searched and think it will be with my "FOR r= last row of old data +1" will do it. But I am not sure how to store the last line of data read so I know where to pick up with each time.

    Here is the code:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 01-25-2016 at 12:37 PM.

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: update existing code to copy and KEEP existing data

    Hey there,

    Please attack a sample workbook.
    You can do so by clicking on "Go Advanced' button at the bottom of the page and then selecting "Paper Clip" icon from the tools above.

    Also, please tell us exactly what you are trying to achieve.

    I just gave a first look at your macro and it seems to be that it can be improved. You are using "Copy" "Paste" in your macro which certainly takes a lot of time to process. While there are other alternatives to copy paste.

    If you can tell what this macro does so I or maybe others can develop a new macro for the same.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  3. #3
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: 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

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: update existing code to copy and KEEP existing data

    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:|
    Last edited by xladept; 01-25-2016 at 01:03 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: update existing code to copy and KEEP existing data

    I got your point.
    Instead of calculating last and new rows, can we just delete all the previous data in all sheets and then update them with all the updated data on the 2016 Rejects sheets. This way we can have the old data and new data both in the parts sheet.

    If yes, then do tell me I will make a macro for the same.

    Also, why do you need separate sheets for each part? maybe you could use filters on the same sheet. However, if its your requirement then that is fine, I will make a macro for that.

  6. #6
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: update existing code to copy and KEEP existing data

    Oops, you already have a solution by xladept .
    Hope that would suffice your requirements.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: update existing code to copy and KEEP existing data

    @ sourabhg98,

    My solution may not be a solution - I didn't intend to stifle any offering by you

  8. #8
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: update existing code to copy and KEEP existing data

    @xladept

    You reputation says it all Sir.
    I am just a newbie, trying to gain as much knowledge about excel as I can.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: update existing code to copy and KEEP existing data

    @ sourabhg98,

    But the way you learn is the way we all learn - by doing and comparing your work to that of others - I will always be learning on this site

  10. #10
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    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.

  11. #11
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    Quote Originally Posted by xladept View Post
    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.....

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: update existing code to copy and KEEP existing data

    Have you posted a sample with data?

  13. #13
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    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.

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: update existing code to copy and KEEP existing data

    Hi Scott,

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

  15. #15
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    Quote Originally Posted by xladept View Post
    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.

  16. #16
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: update existing code to copy and KEEP existing data

    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.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: update existing code to copy and KEEP existing data

    Hi Scott,

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 01-26-2016 at 01:25 PM. Reason: Correction

  18. #18
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    Quote Originally Posted by sourabhg98 View Post
    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?

  19. #19
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: update existing code to copy and KEEP existing data

    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.

  20. #20
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    Quote Originally Posted by sourabhg98 View Post
    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

  21. #21
    Forum Contributor
    Join Date
    07-08-2015
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: update existing code to copy and KEEP existing data

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-19-2014, 04:01 PM
  2. Replies: 11
    Last Post: 11-04-2013, 04:32 PM
  3. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  4. [SOLVED] Correct way to apply an existing VBA code to an existing sheet
    By JO505 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 05:42 PM
  5. Code to copy, paste without replacing the existing data
    By share knowledge in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-04-2011, 05:50 PM
  6. Replies: 1
    Last Post: 08-15-2009, 05:52 AM
  7. VB code to update existing Pivot Table
    By suzetter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2005, 06:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1