Updated Value from 1 Sheet to another

[smartguy]

Hello all,

I have excel in the below format.

Excel 2013/2016
	A	B	C	D
1	Asset tag	CIIM Asset ID	Status	Value
2	E290220	2118195	Yes	123
3	E290085	2118571	Yes	345
4	E291308	2119523	Yes	12
5	E452404	2128911	Yes	12
Sheet1

In Sheet 2 I need to update value.

Excel 2013/2016
	A	B	C	D
1	Asset tag	CIIM Asset ID	Status	Value
2	E291308	2118195
3	E452404	2118571
Sheet2

Answer :

Excel 2013/2016
	A	B	C	D
1	Asset tag	CIIM Asset ID	Status	Value
2	E291308	2118195	Yes	123
3	E452404	2118571	Yes	345
Sheet2

Please help me to provide VBA Code.

Regards,

 

[mumps]

Try:

Sub UpdateData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, desWS As Worksheet, srcWS As Worksheet, fnd As Range, ID As Range
    Set desWS = Sheets("Sheet2")
    Set srcWS = Sheets("Sheet1")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ID In desWS.Range("B2:B" & LastRow)
        Set fnd = srcWS.Range("B:B").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(0, 1).Resize(, 2).Copy ID.Offset(0, 1)
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub