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
Bookmarks