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

Quote:

---

Originally Posted by Lee_of_Exce1

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

---

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"

Quote:

---

Originally Posted by Lee_of_Exce1

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"

---

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.