+ Reply to Thread
Results 1 to 5 of 5

macro to update another workbook with new or updated records

  1. #1
    Registered User
    Join Date
    06-08-2011
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    8

    macro to update another workbook with new or updated records

    Hi All,

    I posted a question yesterday but I realised that my description of the what i'm trying to do wasn't very clear, so here's a new post with 2 workbooks as examples.

    I have one workbook named "open projects", which contains the details and current status of all projects that are open or have only recently been completed. There are typically 2 to 4 worksheets in this file containing project records, with different worksheet for each 2-month period such as "Mar & Apr" or "May & Jun". Other than these, I have 2 other worksheets in the same workbook containing charts and other information.

    I am looking for a macro solution which can be run from this "open projects" workbook to do the following:

    1. Open the "All Projects" workbook (or i can manually open this file before running the macro)

    2. Go through each record in the currently active sheet (example "Mar & Apr") and attempt to find a matching record in "All Projects"'s "data" sheet by comparing the project no. which is the unique 4 digit number in column A of each record.

    3. If a match for the project is found, the entire record for that project would be copied over with the current record from the "Open Project" workbook. If not, it would be added to the work sheet as a new record in the next blank row.

    4. I can then go to the next worksheet (say "May & Jun") and run the macro again to update the changes / addition in that particular sheet. After this, the "data" sheet in "All Projects" would contain the most updated information on all new and existing projects.

    One thing to note is that the macro should only transfer data from a fixed range (such as column A to AB) and not the entire row as additional information may be added to those parts of the "data" sheet.

    The actual records that i work with run into the thousands so a macro solution would be really very helpful. Hope someone has a solution...

    In any case, thank you so much for taking the time to read my post.
    Have a nice day!
    Attached Files Attached Files

  2. #2
    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: macro to update another workbook with new or updated records

    Hi ecc34_11

    This code is in the attached workbook
    Please Login or Register  to view this content.




    It does this
    • Opens All Projects if it's not open
    • Searches through EVERY record in EVERY worksheet in Open Projects if the sheet name is NOT "Charts" or "pending"
    • If a match is found in All Projects for the "Project No", the record in All Projects is replaced with the data from Columns A to AB of the record from Open Projects.
    • If a new record is found it's data is added to the last row of All Projects.
    • It's assumed that BOTH files are in the same folder (this can be modified).
    • It saves and closes the All Projects workbook.
    The code does take some time to run. If there's some way you can define that records have been processed and can be skipped on the next run, that'll certainly help. If you can do that, we can revise the code to incorporate that feature. I didn't upload the All Projects File. Use the original for your testing.

    There's probably better ways to do this but I'm not aware of them. Let me know of issues.
    Attached Files Attached Files
    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.

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

    Re: macro to update another workbook with new or updated records

    hi, ecc34_11, please check attachment, run code "test" in "Open Projects ...xls" file. File "All Projects" is supposed to be on the same path with "Open Projects ...xls" file.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-24-2014
    Location
    FL
    MS-Off Ver
    2010
    Posts
    1

    Re: macro to update another workbook with new or updated records

    Does anyone know how to take the script above and modify it to compare multiple columns instead of just one? I want to have it compare 2 workbooks and if the values in Col A and Col C match between the 2 workbooks then update the entire row. If there is no match, then add the row to the Target. Thanks

  5. #5
    Registered User
    Join Date
    08-15-2014
    Location
    Ireland
    MS-Off Ver
    2010
    Posts
    1

    Re: macro to update another workbook with new or updated records

    Does anyone knows how to change the code so insteed of looking up values from column A till AB , I want to update values from G till P only.

+ 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