+ Reply to Thread
Results 1 to 17 of 17

If value in destination file exists: overwrite whole row, if not: add whole row

  1. 11-26-2021, 06:57 AM #1
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    If value in destination file exists: overwrite whole row, if not: add whole row

    Hi Guys,

    I would like a macro and can't get around this one, quite new in this matter.. can anyone help me out here? Thx!

    From the source file(s) (multiple quotation files, every quotation file has this sheet 'Overview' with the same data in the same sheet & row), I would like to copy the entire row4 into the destination file (one file where all the data from the quotation files come together) starting from row8.

    Conditions:

    1. The macro should run from the source file
    2. If in the source file the value in cel D4 (unique value) already exists in column D in the destination file, the macro should overwrite all data in that particular row of the destination file;
    3. If the value in cel D4 in the source file does not exist yet in column D in the destination file, the macro should paste the data into the next empty row of the destination file.

    Thanks in advance for your help!

    Marc
    Attached Files Attached Files
    Last edited by MarcForier; 11-26-2021 at 06:59 AM.
    Reply With QuoteRegister To Reply
  2. 11-26-2021, 09:56 AM #2
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Hi Marc

    Welcome to the forum...Code assumes Both files are open....

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  3. 11-26-2021, 11:08 AM #3
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Hi Sintek,

    This works great! Big thanks!
    Just one question: can you ad a line to open the destination file, because it is not open all the time?
    Thanks in advance!

    Marc
    Reply With QuoteRegister To Reply
  4. 11-26-2021, 11:12 AM #4
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Assumes wb in same path as Source wb
    Please Login or Register  to view this content.
    Thanks for rep + ...
    Last edited by sintek; 11-26-2021 at 11:22 AM.
    Reply With QuoteRegister To Reply
  5. 11-26-2021, 12:24 PM #5
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Thanks!

    And if the path is different, I change "ThisWorkbook.Path & "TestDestinationFile.xlsm"" in e.g. ."Thisworkbook.C/folder1/folder2/folder3 & "TestDestinationFile.xlsm"?
    Reply With QuoteRegister To Reply
  6. 11-26-2021, 12:35 PM #6
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    No...

    It would be something like this...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 11-30-2021, 06:32 AM #7
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Thanks, you are a big help!

    Have a wonderful day...

    Marc
    Reply With QuoteRegister To Reply
  8. 12-07-2021, 05:33 AM #8
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Hi Sintek,
    Sorry to bother you once more, but the values copied in the destination file seem to be not 'a value', so formulas applied to these cells don't work. How do I address this problem without having to correct the copied line all the time?
    Grtz,
    Marc
    Reply With QuoteRegister To Reply
  9. 12-07-2021, 05:38 AM #9
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Please elaborate with an example...
    Reply With QuoteRegister To Reply
  10. 12-07-2021, 06:33 AM #10
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    see file attached..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 12-07-2021, 08:11 AM #11
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    see file attached..
    And...What am I looking at...???
    Reply With QuoteRegister To Reply
  12. 12-07-2021, 09:22 AM #12
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    this is the destination file, where the data from the source file are copied and pasted; in this case, the first line of data (row 8) in this file was overwritten, because the value in cell D8 of the source file already existed in column D of the destination file. (if the value of cell D8 of the source file didn't excist yet in column D of the destination file, row 8 of the source file would have been added at the first empty row of the destination file).
    All the new data in the cells of row 8 give the error message that the data in the cell is noted as text instead of value; this happens every time I run the macro from the source file. so I have to correct every line that has been added or overwritten.
    I assume this has something to do with how the numbers are defined in the VBA code you wrote ;-)
    Reply With QuoteRegister To Reply
  13. 12-07-2021, 09:29 AM #13
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    All the new data in the cells of row 8 give the error message that the data in the cell is noted as text instead of value
    The Number Formats for data in Row 8 is as follows...
    Date, General, General, General, Custom etc etc etc
    I imagine you know what you are referring to...I do not as I am not in your mind...
    As per Post 3
    This works great! Big thanks!
    Explain to me in layman's terms what you want this code to do...What has changed in your actual data files...
    Reply With QuoteRegister To Reply
  14. 12-07-2021, 10:21 AM #14
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    The vba works indeed great. Just noticed this problem later ;-); I changed nothing in the two files.

    Data starting from row8 downwords, from column F to V included should be in €, as a value, not as text.
    The Number formats of the cells isn't the problem; the way the value is written in the cell is: the numbers are written as 'text', not as a 'number'; that is why there is an error on every cell in row 8 (the other rows I already corrected manualy)
    Hope this makes it more clear for you?
    Reply With QuoteRegister To Reply
  15. 12-07-2021, 10:37 AM #15
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Perhaps I am misunderstanding...or missing something...
    When I run the provided code above with your supplied sample files above and then run code below for Destination File...All are numeric...
    Please Login or Register  to view this content.
    The file you supplied in Post 10 however are all stored as text...

    Edit...
    I am guessing that your actual files have a different setup and thus the provided code does not work...
    Upload two new files and I will have a look and provide alternate solution...

    Try this approach...
    Please Login or Register  to view this content.
    Last edited by sintek; 12-07-2021 at 11:04 AM.
    Reply With QuoteRegister To Reply
  16. 12-09-2021, 04:36 AM #16
    MarcForier
    MarcForier is offline
    Registered User
    Join Date
    11-26-2021
    Location
    Brussels
    MS-Off Ver
    2021
    Posts
    9

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    This seems to do the trick.
    Thanks again for your help !
    Marc
    Reply With QuoteRegister To Reply
  17. 12-09-2021, 04:38 AM #17
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,236

    Re: If value in destination file exists: overwrite whole row, if not: add whole row

    Glad you got it sorted...Happy Coding...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA for Autosaving a file to .pdf BUT not allowing overwrite if file already exists
    By alex900_6 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-05-2021, 02:50 PM
  2. Copy and paste to new worksheet but overwrite if data already exists.
    By digimon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2015, 11:17 PM
  3. For a list of entries, overwrite in another tab if entry exists, copy/paste at end if not
    By darkyam in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-19-2014, 11:12 PM
  4. [SOLVED] VBA to Promt if PDF exists and to overwrite - Close Existing File first
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2014, 03:09 AM
  5. [SOLVED] Stop overwrite of file if it already exists, no popup wanted
    By LoneWolf3574 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-06-2012, 07:24 AM
  6. Help with vba, if record exists overwrite ?
    By vsantoro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2011, 01:12 PM
  7. If sheets exists in another workbook overwrite it
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-27-2009, 12:57 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1