finding the value and paste in another sheet .

Hello
I need your help , I have a list that the weight column are not static mean it is in different column i need find the weight column from sheet 1 and garb the value after weight column and paste them in sheet 2 as the below sample .
you can see it in attached images . i need VBA that can do it automatically .
really appreciate for your help and time .

Attachment 20953

Start wirh

Code:

---

Dim FoundSrc As Range
Dim FoundDest as Range

Set FoundSrc = Sheet1.Rows(1).Find("Weight(kg)")
Set FoundDest = Sheet2.Rows(1).Find("Weight(kg)")
If not FoundSrc is Nothing And Not FoundDest is Nothing then
FoundDest.Offset(,1) = FoundSrc.Offset(,1)

---

You might have to edit the Find Parameters.

Thanks you so much for your help but our sheet2 is empty and must paste the data that found to sheet2 .

Research the offered code and adjust as needed.

Code:

---

Sub test()
    Dim FoundSrc As Range
     
    Set FoundSrc = Sheet1.Rows(1).Find("Weight(kg)")
    If Not FoundSrc Is Nothing Then
        Sheet1.Range(FoundSrc.Address).EntireColumn.Copy
        Sheet2.Paste (Cells(1, 1))
    End If
End Sub

---

Really thank you for you try and help , please check the attached excel file , the sheet2 is result mean when run the VBA the result must be like sheet 2

Attachment 20956

EH?

Code:

---

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

---

Really appreciate for your help and working very well .