+ Reply to Thread
Results 1 to 26 of 26

VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Goal: To compare the data in 2 Sheets (a master and template/replica) update the Master Sheet with any updated data(cells) from the templates/replicas of the Master without modifying or replacing matching Data.

    For Example:
    Master sheet shows: in Cell A1= Test1, Cell B1=Test2, Cell C1=Test4
    Replica sheet which will be used to update the master shows:*in Cell A1= Test1, Cell B1=Test3*Cell C1=Test4
    After macro has run the Master should now show:*in Cell A1= Test1, Cell B1=Test3*Cell C1=Test4

    Issue: I believe that the code I have is correct for comparing the data between the Master sheet and the updated template/replica, but I cannot work out how to update (merge/replace) this data on the Master.

    The current code:


    Option Explicit

    Sub UpdateMasterSheet() 'Updates a master spreadsheet (Sheet1) using Data from (Sheet2)*

    * * Dim varSheetA As Variant
    * * Dim varSheetB As Variant
    * * Dim strRangeToCheck As String
    * * Dim iRow As Long
    * * Dim iCol As Long

    Application.ScreenUpdating = False

    * * strRangeToCheck = "A1:V1000"
    * * ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
    * * Debug.Print Now
    * * varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    * * varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
    * * Debug.Print Now

    * * For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    * * * * For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
    * * * * * * If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
    * * * * * * * * ' Cells are identical.
    * * * * * * * * ' Do nothing.
    * * * * * * Else
    * * * * * * * * ' Cells are different.
    * * * * * * * * ' Code goes here for whatever it is you want to do. - Here I want the code to tell the VBA to replace the changed cell on the master with the updated data on the template/replica
    * * * * * **
    * * * * * * End If
    * * * * Next iCol
    * * Next iRow
    **

    End Sub


    At this stage I only need to update the Master Sheet using other sheets, but if you have a variant for workbooks also that would be great.

    I have attached my test spreadsheet where the Master is 'sheet1' and the template/replica is 'sheet2'. The data on sheet1 should be updated with any new data on sheet2 without modifying cells that contain matching data. Currently I have this partially working using a terrible method of copy and paste then delete original row and my VBA currently creates duplicates - just awful. But I am new to VBA and would greatly appreciate any assistance and hope to provide my own assistance in the future.


    Cheers

    Lee
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    365
    Posts
    129

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    First question I would ask is:

    Wouldn’t copying the whole range from sheet 2 and pasting it in place of the existing data on sheet 1 essentially yield the same results as only updating cells on sheet 1 that have been changed on sheet 2, since any unchanged cells on sheet 2 would simply be replacing the same identical value already in the cells on sheet 1? Or am I missing something?

    In other words, your “for example” above basically says that after the macro is run, the master sheet shows exactly the same data as the replica sheet... so why not simply replace the master with the replica, instead of comparing values and only replacing the ones that are different?

  3. #3
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    https://www.ozgrid.com/forum/forum/h...sheet-workbook
    same as post
    "ThankyouFor Attention * And Your Help!!"

  4. #4
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by Arnold Layne View Post
    First question I would ask is:

    Wouldn’t copying the whole range from sheet 2 and pasting it in place of the existing data on sheet 1 essentially yield the same results as only updating cells on sheet 1 that have been changed on sheet 2, since any unchanged cells on sheet 2 would simply be replacing the same identical value already in the cells on sheet 1? Or am I missing something?

    In other words, your “for example” above basically says that after the macro is run, the master sheet shows exactly the same data as the replica sheet... so why not simply replace the master with the replica, instead of comparing values and only replacing the ones that are different?
    Thanks for your reply Arnold,*

    I don't think my explanation was perfect. Basically:
    - The master sheet has for example 1000 rows of data
    - The template/replica sheet has the exact same format (As it is an extract from the master), but may only contain 20 rows (relating to a particular team) for example.
    - There may be 10 different sheets coming back with extracted rows from the master which have now been updated and need to be either be copied in to the Master sheet to replace the matching row (using a unique ID in column A) or the updated cells need to replace*the original cell data in the master sheet.
    *
    This process will be a regular occurrence so I want to have a VBA macro that I can adapt to different scenarios.

    Thanks**

    ​​​​​​​Lee

  5. #5
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by daboho View Post
    URL
    same as post
    Cheers daboho, I have added my query to this other forum too as I was not sure of the response success, but unfortunately the solutions provided by Graha_Karya on the the other forum did not work.*

  6. #6
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Basically all I believe that I need is 1 line of VBA code:

    Just above the 'End If', where it says:

    "Code goes here for whatever it is you want to do. - Here I want the code to tell the VBA to replace the changed cell on the master with the updated data on the template/replica"

  7. #7
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    365
    Posts
    129

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Okay, I see what you mean now I think... so, in column A, you have unique row identifier data, let's assume simply the cell name entered as text, i.e. "A1", "A2", "A3", etc... all the way down to "A1000" in row 1000. You want to be able to extract a group of rows, say rows 40-59, and give just those rows to a user and that user will make changes to some cells, but not all cells. Then you want to take what they give you back and using a macro, replace your existing rows 40-59 with the new data your user has given you for rows 40-59, without changing anything in your existing rows 1-39 or 60-1000? Does that sound like what you're wanting?

  8. #8
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by Arnold Layne View Post
    Okay, I see what you mean now I think... so, in column A, you have unique row identifier data, let's assume simply the cell name entered as text, i.e. "A1", "A2", "A3", etc... all the way down to "A1000" in row 1000. You want to be able to extract a group of rows, say rows 40-59, and give just those rows to a user and that user will make changes to some cells, but not all cells. Then you want to take what they give you back and using a macro, replace your existing rows 40-59 with the new data your user has given you for rows 40-59, without changing anything in your existing rows 1-39 or 60-1000? Does that sound like what you're wanting?
    Yes, perfect explanation, the only thing that I failed to mention is that the extracted rows are not always in order, for example they may contain row 40, 46, 70, and 81. So it makes it a little trickier to paste in.
    Cheers
    Last edited by Lee_of_Excel; 11-08-2018 at 01:45 AM.

  9. #9
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    365
    Posts
    129

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Okay, let me think on that one for a bit then. I can't get your sample file to work on my computer, it says there are compiling issues because I'm on a 64 bit system... but I think I can throw something basic together from scratch that would do what you're needing.

    I almost see this working better with more of your process automated, and controlled by a userform. Basically, a userform you'd open from within your master file that asks you for your start and end lines to extract, and then places the values from that range into another workbook that has a password protected "import" button that would overwrite only the lines that have the same identifier data in column A on your master sheet, leaving the rest of the master intact. Does that sound like a solution that would work?

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Just curious to know...

    Row size differs, but how about column size?
    Seems you don't have header...

  11. #11
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    365
    Posts
    129

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by Lee_of_Excel View Post
    Yes, perfect explanation, the only thing that I failed to mention is that the extracted rows are not always in order, for example they may contain row 40, 46, 70, and 81. So it makes it a little trickier to paste in.
    Cheers
    Didn't see that part when I was typing my last reply...

    So, what's your process for extracting the lines then? Do you do it by filtering and then copy/paste? I would think that it shouldn't be any tricker to paste back in than it is to pull the lines out in the first place, depending on how you're pulling them.

  12. #12
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by Arnold Layne View Post
    Okay, let me think on that one for a bit then. I can't get your sample file to work on my computer, it says there are compiling issues because I'm on a 64 bit system... but I think I can throw something basic together from scratch that would do what you're needing.

    I almost see this working better with more of your process automated, and controlled by a userform. Basically, a userform you'd open from within your master file that asks you for your start and end lines to extract, and then places the values from that range into another workbook that has a password protected "import" button that would overwrite only the lines that have the same identifier data in column A on your master sheet, leaving the rest of the master intact. Does that sound like a solution that would work?
    Thanks Arnold,

    I have attached an updated spreadsheet with updated instructions.

    The Master Sheet - Sheet1 contains the complete data (Master data).

    Sheet2 is an extract of Sheet1, but only contains selected rows (currently the selected rows are manually copied and pasted from sheet1 to sheet2 and send to other people to make updates).

    A user may edit cells in sheet2 (I have highlighted these in red).

    Once sheet2 cells have been edited, I would like the VBA macro to compare Sheet1 against Sheet2 and copy/insert the changed cells into Sheet1.

    (There are better ways to do this exact process, but currently we need to continue with manually extracting rows from sheet1, pasting into sheet2 and sending them to people to edit.)

    This will be repeated regularly and cannot be set up using simple lookups as i would like to modify the VBA to suit future purposes also.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by Arnold Layne View Post
    Didn't see that part when I was typing my last reply...

    So, what's your process for extracting the lines then? Do you do it by filtering and then copy/paste? I would think that it shouldn't be any tricker to paste back in than it is to pull the lines out in the first place, depending on how you're pulling them.
    Unfortunately the extracted rows are currently copied/pasted manually into a sheet, I will be working on a solution for this later but at this stage it's the only way
    Last edited by Lee_of_Excel; 11-08-2018 at 07:38 PM.

  14. #14
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by jindon View Post
    Just curious to know...

    Row size differs, but how about column size?
    Seems you don't have header...
    Hi Jindon,

    I should have included a better spreadsheet, I have now attached an updated spreadsheet with rows and columns, which will grow over time

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    This updates Sheet1 from Sheet2 if there is match, regardless of change made or not and add new line(s) at the bottom if there is.

    If the speed is slow, need to change the method...
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by jindon View Post
    This updates Sheet1 from Sheet2 if there is match, regardless of change made or not and add new line(s) at the bottom if there is.

    If the speed is slow, need to change the method...
    Please Login or Register  to view this content.
    Excellent, thanks very much Jindon, this seems to be working great, I will test it on the complete data. Cheers

  17. #17
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Solved Thanks Jindon, this works perfectly.

    Cheers
    Lee

  18. #18
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Quote Originally Posted by jindon View Post
    This updates Sheet1 from Sheet2 if there is match, regardless of change made or not and add new line(s) at the bottom if there is.

    If the speed is slow, need to change the method...
    Please Login or Register  to view this content.

    Hi Jindon and All,

    It has been a while, but can I please ask one more favour.

    With your code, how can I use the split function to pull data from 2 tables, which may be different in size?

    Currently I have Trainee names in Column B, from B4:B7, followed by their preferences of sites in row (C4:H7),

    Then I have Sites from B12:B22, followed by their preference of trainees in row (C12:H22)

    The amount of trainees and amount of sites may vary and the number of their preferences may vary.

    I want to try and use your VBA code to recognise data entered in the 2 tables.

    I have enclosed the spreadsheet and screenshot as an example.

    Thanks again

    Lee

    The 'Non Working' code is:

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    I don't think this is related to your original question.

    If you open a new thread for it, I would be looking at it.

  20. #20
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Yes sorry Jindon, I accidentally posted it to my wrong thread, updating now.

    Should be on:

    https://www.excelforum.com/excel-pro...ml#post5108089

  21. #21
    Registered User
    Join Date
    01-23-2020
    Location
    Scotland
    MS-Off Ver
    0365
    Posts
    18

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Folks, I have to admit I am not the brightest and looking at this post it looks like, in General, what I am trying to achieve. The challenge that I have is that I cannot seem to put it all together to get where I need and I am hoping that you can provide some clarity. I too, have a master sheet (Audit Tracker) that I am trying to automatically update when any changes are made to sheets that have been sent out for update. The sheets will all be in the same location with the same fields etc.

    I have attache a sample of the sheets (there will 7 "child" sheet sources when I am finished) and was hoping that you could put me on the right track.

    Any help will be valuable.

    Thanks in advance
    Attached Files Attached Files

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Big_Kev007,

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  23. #23
    Registered User
    Join Date
    01-23-2020
    Location
    Scotland
    MS-Off Ver
    0365
    Posts
    18

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Will start one now Jindon, thought I was doing the right thing, cheers

  24. #24
    Registered User
    Join Date
    09-11-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Hi Jindon,

    Thanks for this code, it resolves one part of my requirement.. I'm having multiple people work the same data as master sheet and required to consolidate the changes and add new data (row) to master sheet.

    i require below changes in the above code. If you help me with that really appreciated.

    1. There'll will multiple sheets similar to sheet1 (Master) (will run a separate macros to consolidate the worksheet from diff workbook saved in the location - good if you add that also)
    2. Macro need to compare the data in each sheet with Master and move the changed row from other sheets to "Update" sheet with last column as sheet name
    3. Highlight the duplicate rows (Unique ID) in the update sheet and ask the user to fix the duplicate
    4. Next macro to update the master with "Update" sheet. First check for duplicate in Update sheet and not proceed further until the duplicates are fixed
    5. Wish to have command buttons (consolidate, Check, Update) in the first sheet to perform the above actions ne by one

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    kanag1307,

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  26. #26
    Registered User
    Join Date
    09-11-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: VBA to compare and update data on Master Sheet/workbook from another Sheet/workbook

    Hi.. Created a new thread "Macro to consolidate and update Master the sheet with updates / additions". Look forward for your help..

+ 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. [SOLVED] 1 master sheet to update multiple tabs all in same workbook
    By dawondr in forum Excel General
    Replies: 2
    Last Post: 08-17-2018, 11:09 AM
  2. [SOLVED] Macro to export each sheet after Master Sheet as separate workbook including Master
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-02-2017, 09:06 AM
  3. Replies: 0
    Last Post: 10-21-2015, 03:03 PM
  4. Data update in active sheet and also to master workbook through vba
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2013, 08:42 AM
  5. [SOLVED] Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Sheet
    By Jennasis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2013, 06:11 AM
  6. [SOLVED] Master workbook to pull data from second workbook into seperate sheet
    By djm601 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-16-2013, 09:24 AM
  7. [SOLVED] Search for heading in data sheet and copy range to corresponding sheet in master workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-07-2012, 10:02 AM

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