I agree with the earlier answers: it appears this requirement can most easily be met with Excel formulae.
This answer is largely advice on how to develop VBA solutions when necessary.
I do not believe that searching for a large code block that appear to vaguely match your requirement and then amending that block is the correct approach. It is likely that any code block you find will contain VBA functionality you do not understand. Do you know what dictionaries are? Do you know how to use dictionaries? Would a dictionary be the right solution on this occasion?
If you are going to write VBA macros, you must learn VBA. Search the web for "Excel VBA tutorial". There are many to choose from so pick one that matching your learning style. I prefer books. I visted the library in the nearest large town and reviewed their Excel VBA primers. I borrowed a few so I could try them at home. Finally I visited a bookshop and bought the one most suitable for me. I would dip into that book whenever necessary. Whichever approach is right for you, the time spent learning VBA will quickly repay itself.
You must break your requirement down into simple steps for which you already know the VBA or for which you could expect to find some useful code in your book or if you searched the web.
You want to update one worksheet from another. In general, I never update a worksheet because if something goes wrong before the macro is finished, I have corrupted the worksheet. I normally, create a new worksheet and build it from the source worksheets. If anything goes wrong, restarting is easy. If appropriate, I will delete the original worksheet when the new one is complete. Do you know how to create a new worksheet or delete an existing one? You can search for "Excel VBA: create worksheet" and expect to find a useful answer. However, I would start the macro recorder and create some worksheets and delete them from the keyboard. I would then examine the resultant code to discover the statements that create and delete worksheets.
In this case you are adding new columns to the end of the existing rows so there would be no problem with restarting the macro.
The heart of your macro will be a loop that examines every row in Sheet1. Any book on online tutorial will show you how to do that. Searching for "Excel VBA: find last row of worksheet" will give you relevant code.
I could go on but I hope I have given you an adequate introduction to designing and creating a solution to a requirement,
There are many similar method of meeting your requirement. Which method is best is not always obvious with a small requirement so I have picked a method what I hope is easy to understand.
Option Explicit ' Look up thi statement to see why its inclusion is good practice
Sub MergeSheets()
' Using constants for columns means your code:
' * takes longer to write
' * is easier to read and debug
' * can be updated quickly if a column moves
' Note my naming style. I start with what I use the variable or constant for.
' Eg: "Col" for column. I then add words that narrow down the use until I
' have a unique name. I am not asking you to like my style but to develop a
' style of your own. I can look at macros I wrote years ago and immediately
' know what all the variables are which is a big help.
' I have used "One" and "Two" to identify the sheets because "1" and "2" are
' too short. However, you should give meaningful naems to your worksheets.
Const ColOneSN As Long = 1
Const ColOneProduct As Long = 2
Const ColOneDateFinished As Long = 3
Const ColOnePerson As Long = 4
Const ColOneDuration As Long = 5
Const ColOneDurationUnit As Long = 6
Const ColTwoSN As Long = 1
Const ColTwoDateFinished As Long = 2
Const ColTwoPerson As Long = 3
Const ColTwoDuration As Long = 4
Const ColTwoDurationUnit As Long = 5
Dim DateFinished As Date
Dim Duration As Long
Dim DurationUnit As String
Dim Person As String
Dim Rng As Range
Dim RowOneCrnt As Long
Dim RowOneLast As Long
Dim SN As String
Dim WshtOne As Worksheet
Dim WshtTwo As Worksheet
Set WshtOne = Worksheets("Sheet1")
Set WshtTwo = Worksheets("Sheet2")
' Assume column widths in WshtTwo are corect and use them for WshtOne
WshtOne.Columns(ColOneDateFinished).ColumnWidth = WshtTwo.Columns(ColTwoDateFinished).ColumnWidth
WshtOne.Columns(ColOnePerson).ColumnWidth = WshtTwo.Columns(ColTwoPerson).ColumnWidth
WshtOne.Columns(ColOneDuration).ColumnWidth = WshtTwo.Columns(ColTwoDuration).ColumnWidth
WshtOne.Columns(ColOneDurationUnit).ColumnWidth = WshtTwo.Columns(ColTwoDurationUnit).ColumnWidth
RowOneLast = WshtOne.Cells(Rows.Count, ColOneSN).End(xlUp).Row
' Start value for For Loop assumes no header row as in your example.
' You could use a constant such as RowOneDataFirst if a header line
' might be added later or if the number of lines mught change.
For RowOneCrnt = 1 To RowOneLast
' Extract SN to search for from WshtOne
With WshtOne
SN = .Cells(RowOneCrnt, ColOneSN).Value
End With
With WshtTwo
' Search SN column of WshtTwo for SN
Set Rng = .Columns(ColTwoSN).Find(What:=SN)
If Rng Is Nothing Then
' This SN not found
' Add code for this sitation
Else
' SN found
DateFinished = .Cells(Rng.Row, ColTwoDateFinished).Value
Person = .Cells(Rng.Row, ColTwoPerson).Value
Duration = .Cells(Rng.Row, ColTwoDuration).Value
DurationUnit = .Cells(Rng.Row, ColTwoDurationUnit).Value
End If
End With
If Not Rng Is Nothing Then
' Copy values into WshtOne
With WshtOne
.Cells(RowOneCrnt, ColOneDateFinished).Value = DateFinished
.Cells(RowOneCrnt, ColOnePerson).Value = Person
.Cells(RowOneCrnt, ColOneDuration).Value = Duration
.Cells(RowOneCrnt, ColOneDurationUnit).Value = DurationUnit
End With
End If
Next
End Sub
