+ Reply to Thread
Results 1 to 10 of 10

copy+paste values if value does not exist

  1. #1
    Registered User
    Join Date
    11-12-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2007
    Posts
    59

    copy+paste values if value does not exist

    Dear all,

    I know that these days I should focus on something else, but I just cannot take it out of my mind and cannot find another way to do it but with macros. So I need some help with the following:

    1. When Excel file opens up the macro copies from H11 to H16 and paste special with truncate in sheet2 in the next available empty row from A2, if date (Sheet1!H11) does not exist in Sheet2!column A:A.

    2. Right after that, the macro copies the values from G11 to G16 in sheet1 and pastes the values from H11 to H16 (sheet1).


    3. G11 date is equal to Filedatetime: C:\Users\admin\Desktop\book1.xls

    The macro in point 2 two it's easy and I have even managed to make it when Excel opens up. Point 1 is too complicated for me because of the if condition. Point 3 I have tried many times adding the path but always get sseveral and different errors. Don't know exactly what I am doing wrong in there.

    Many thanks all for your help. Attached the example.
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: copy+paste values if value does not exist

    You can link your workbooks in formulas

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-12-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: copy+paste values if value does not exist

    The point of this thread is because the values from G11 to G16 will change on a weekly basis, as soon as the original data source is updated(G11 to G16 contain Index/Match formulae to book1.xls ). That's why I need to keep historical records of them in sheet2. Formulas will only capture actual values.

    I hope I have explained myself clearly.

    Many thanks Davesxcel!

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: copy+paste values if value does not exist

    Hi siroco79

    Point 1 is relatively simple, except, you say
    truncate in sheet2
    Do you mean Transpose?

    Point 2 is also relatively simple

    Point 3...I have no idea what you're after...post you're expected outcome and how you arrived at it.

    Try this Code in your WorkBook Open Event...it should accommodate Points 1 & 2
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: copy+paste values if value does not exist

    Hi siroco79

    Sorry, I missed the indicated Line of Code
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-12-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: copy+paste values if value does not exist

    Hi Jaslake,

    First of all many thanks for your reply and for your support.

    Clarifications:

    Point 1: Yes, you are totally right. I meant transpose and not truncate. Many thanks!

    Point 3: I need to extract the date (last modified/last saved date) of a external workbook or Excel file and this date to be reflected in cell G11. File location: C:\Users\admin\Desktop\Book1.xlsx.

    Feedback:

    1.When I open the workbook nothing happens, unless I run the code from the Visual Basic module. I also tried to save it with the declaration Workbook open, but never worked. I would like the action to happen when opening the workbook.

    2. The code cuts the values from H11 to H16 instead of copying, is that something we can change? I need to see those values. I assume that you've gone for cutting the values because the next action would copy values (H11 = format cell = date) from G11:G16 to H11:H16, correct? The thing is that I cannot see that part of the code. I have recorded a macro to do that but every time it runs it copies the value as a number, even when I saved that cell with date format. Can you help with that?

    Many thanks, really!!!!!!

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: copy+paste values if value does not exist

    Hi siroco79

    I'll need to get back to you after the Holiday...sorry...

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: copy+paste values if value does not exist

    Hi siroco79

    The Code appears to work for me on opening the File.

    Regarding this
    The code cuts the values from H11 to H16 instead of copying, is that something we can change?
    This line of Code does that
    Please Login or Register  to view this content.
    I'm sure I don't understand what you're after. If you don't clear H11-H16 you'll ALWAYS get this message on Workbook Open
    MsgBox "Record Already Exists"
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-12-2013
    Location
    Geneva
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: copy+paste values if value does not exist

    Sorry for the long delay Jaslake!

    I managed with your code apart from a few minor modifications and I also added by myself to implement FileDateTime. So, I am going to mark this thread as resolved.

    Many thanks!

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: copy+paste values if value does not exist

    You're welcome...glad I could help.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ 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. copy formula and paste for new data added and autofill.....and paste special values
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2014, 07:40 AM
  2. Vba - find next empty column, insert column, copy paste values & copy paste formulas
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 12:43 PM
  3. VBA Copy-paste values with changing paste location
    By booost in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-16-2012, 10:39 AM
  4. [SOLVED] Cut, Copy, Paste & Delete Rows If Value Does Not Exist
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 03:41 AM
  5. Replies: 4
    Last Post: 06-18-2006, 01:10 PM

Tags for this Thread

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