+ Reply to Thread
Results 1 to 6 of 6

Modify vba coding so that the database updates correctly

  1. #1
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Modify vba coding so that the database updates correctly

    Hi everyone, need any help from forum guru's here.

    Attached is the sample excel file with the code. MySampleDBs.xlsm

    I use vba code in my attached sampledb that updates sheet "data" if there are newer data in sheets "new" according to the latest dates, basically it checks dates in sheet "data" and compares it with sheet "new", if there is newer dates in "new", it copies the whole column with the newer date and pastes it next to last used column in "data".

    However the current code only adds a single column from the new ones, and loop copies only on that column instead of pasting to the next column after it.

    I need the code to copy the latest dated column and loop until all new dated columns have been copied.

    Please help out. Thanks.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Modify vba coding so that the database updates correctly

    maybe so
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Modify vba coding so that the database updates correctly

    Quote Originally Posted by nilem View Post
    maybe so
    Please Login or Register  to view this content.
    Hi Nilem, its you again helping me out.

    Nice code

    Sigh, It took me a day to find, design and modify the current code. But your code is so much compact and cleaner. Works like a charm too.

    Only thing is in my database there may be blank rows between the datas so the property .CurrentRegion does not take it into account.

    Could you please edit it until it copies till the end of the columns with data?

    Thanks.

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Modify vba coding so that the database updates correctly

    try
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Modify vba coding so that the database updates correctly

    Awesome Nilem, it works.

    Now I just need one more adjustment please.

    Referring to attached image. The dates in sheet "new" have only one duplicate for each date in a row. The date is same, but not the data held.

    samples.JPG

    Need your help to edit the macro to form two similar macros.

    The first one checks newer dates starting from left to right, copies newer dated columns and stops when the dates become discontinous.

    The second one is same as the first, but starts to checks from where the first stops until the end.

    What I mean is that the first macro copies the first set of available column with newer dates , and the second macro copies column with the second set duplicate of newer dates.

  6. #6
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Modify vba coding so that the database updates correctly

    Just to update, I solved the problem already by splitting the datasource to respective sheets, and derived data into my current db separately.

    Couldnt have done it without your code Nilem, it was the key to complete my project.

    Thank you very much Nilem.

+ 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