EH?
Sub TEST()
I = 1
Do Until Sheet1.Cells(I, 1).Value = ""
Sheet2.Cells(I, 1).Value = Sheet1.Cells(I, 1).Value
Sheet2.Cells(I, 2).Value = Sheet1.Cells(I, 2).Value
Sheet2.Cells(I, 3).Value = Sheet1.Cells(I, 3).Value
Sheet2.Cells(I, 4).Value = "Weight(kg)"
J = 3
Do Until Sheet1.Cells(I, J).Value = ""
If Sheet1.Cells(I, J).Value = "Weight(kg)" Then Sheet2.Cells(I, 5).Value = Sheet1.Cells(I, J + 1).Value
J = J + 1
Loop
If Sheet2.Cells(I, 5).Value = "" Then Sheet2.Cells(I, 5).Value = "NOT FOUND"
I = I + 1
Loop
End Sub