Lee_of_Excel
New Member
- Joined
- Nov 7, 2018
- Messages
- 5
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 couldn't seem to attach 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
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 couldn't seem to attach 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