PDA

View Full Version : [SOLVED] finding the value and paste in another sheet .



parscon
11-14-2017, 03:53 AM
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 .

20953

SamT
11-14-2017, 06:32 AM
Start wirh

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.

parscon
11-14-2017, 06:49 AM
Thanks you so much for your help but our sheet2 is empty and must paste the data that found to sheet2 .

SamT
11-14-2017, 08:00 AM
Research the offered code and adjust as needed.

MINCUS1308
11-14-2017, 08:09 AM
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

parscon
11-14-2017, 08:48 AM
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

20956

MINCUS1308
11-14-2017, 09:03 AM
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

parscon
11-14-2017, 11:41 AM
Really appreciate for your help and working very well .