+ Reply to Thread
Results 1 to 16 of 16

Copying Data without overwriting (edit macro)

  1. #1
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Exclamation Copying Data without overwriting (edit macro)

    I have the following macro that copies data from sheet1 and pastes it to sheet2.

    I want to edit this macro to not overwrite the data in sheet2 when pasting in the data from sheet1. it should only paste in the data if the respective cell is empty. if something is already there it should just move on to the next cell.

    Previously what this macro did was it saved the old data as a comment in the respective cell (sheet2) and pasted in the data from sheet1.

    JN


    Please Login or Register  to view this content.
    posting here too:
    http://www.mrexcel.com/forum/showthread.php?t=532593
    Last edited by jimmy_nora; 03-01-2011 at 10:40 AM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copying Data without overwriting (edit macro)

    hi, jimmy_nora, I have a question for you. So the code finds matching value in Sheet2, obviously the value is not empty, we move one row down, cell has value as well, move down again .... So what's the point to use search for value then? Or you have the data in groups and that will anyway find empty cell within required group?

  3. #3
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    Let me explain a bit detail how the macro works.
    - We compare every value in sheet1 column C with sheet2 coloumn A to find a match

    If there is a match then do this in that respective row of the matching value in sheet2:
    - copy the data from the respective columns in sheet1 (D,F,H) and paste it in the sheet2 columns F, D, I respectively.

    Now what I want is that when the value are to be pasted in the columns of sheet2 they should not overwrite the already entered values in the respective cell. it should just move on.

    I hope this clears your question.
    Last edited by jimmy_nora; 03-01-2011 at 06:11 AM.

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copying Data without overwriting (edit macro)

    it's always better to have sample of the workbook, though you can test it by yourself:

    Please Login or Register  to view this content.
    Last edited by watersev; 03-01-2011 at 06:39 AM.

  5. #5
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    here is the sample file

    in my real project there are a lot of columns (about 30) between both sheets that I have to copy between. can you point out where I need to modify the code to accommodate the additional columns ?
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copying Data without overwriting (edit macro)

    could you please test the code "CopyData1"
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    Yes for this case the macro works.
    what changes do i need to make (apart from these on the top of the code NbCol , ColSRC , ColDEST ) to copy the data between the following 16 columns from sheet 1 to sheet 2

    Sheet1 : C,A, B, N, M,E,H,F,G,J,K,L, I, Q, D, O
    Sheet2: D,O, Q, V, AF, AG, AH, AM, AN, AR, AS, AT, AY, BA, BK, BL

    And also we are now comparing all the values in Sheet1 Column P (row 2 and onwards) with Sheet2 Column A (row 5 and onwards) to find a match before copying the data over.


    Can you please edit your code to accommodate these two changes .
    Last edited by jimmy_nora; 03-01-2011 at 07:53 AM.

  8. #8
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    i have uploaded an updated version of the file to show what I mean. please test your macro on this when you make changes.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copying Data without overwriting (edit macro)

    ok, I'll have a look. As far as I see it's a completely separate question, right?
    Last edited by watersev; 03-01-2011 at 08:43 AM.

  10. #10
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copying Data without overwriting (edit macro)

    please check attachment, run code "CopyData1"
    Attached Files Attached Files

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Copying Data without overwriting (edit macro)

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    Read this to understand why we ask you to do this.

    Note: this means posting links to ALL your cross-posts.
    Remember what the dormouse said
    Feed your head

  12. #12
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    Quote Originally Posted by romperstomper View Post
    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    Read this to understand why we ask you to do this.

    Note: this means posting links to ALL your cross-posts.
    I did provide a link to a cross post. It is in the OP

  13. #13
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    @Watersev

    Thank you very much. Your code works like a beauty. Still checking my results though. So far so good.

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Copying Data without overwriting (edit macro)

    I said ALL your cross posts. You did not link to this one at OzGrid, for example. (there may be others for all I know)

  15. #15
    Registered User
    Join Date
    10-20-2010
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Copying Data without overwriting (edit macro)

    Please can you re-check the macro-code because it is overwriting the old data even when it should not ?
    It is only supposed to fill in the empty cells and skip the cells with data.

  16. #16
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Copying Data without overwriting (edit macro)

    hi, jimmy_nora, please check attachment
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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