Results 1 to 26 of 26

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

Threaded View

  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

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