PDA

View Full Version : Move data from sheet 1 to 2



ksp
01-06-2018, 06:42 AM
Hi, I have data is sheet1 with Id, date and type in col A,B,C. In sheet 2 I have Id name in col A and B but the date is in row1(1-30) days. I want the code to lookup the date in sheet1,col b with the correaponding id and paste it in sheet2 against the corresponding I'd and under the given date of the value in type col in sheet1

offthelip
01-06-2018, 03:42 PM
this should do it for you:



Sub test()
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 3))
End With


With Worksheets("Sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
Range(.Cells(2, 2), .Cells(lastrow, 33)) = ""
outarr = Range(.Cells(1, 1), .Cells(lastrow, 33))
End With
' loop thru sheet1 id
For i = 2 To lastrow
'looop thru sheet 2 id
For j = 2 To lastrow2
If inarr(i, 1) = outarr(j, 1) Then
' match found so go find date
For k = 3 To 33
If inarr(i, 2) = outarr(1, k) Then
'date found copy in the type
outarr(j, k) = inarr(i, 3)
Exit For
End If
Next k
End If
Next j
Next i


With Worksheets("Sheet3")
Range(.Cells(1, 1), .Cells(lastrow, 33)) = outarr
End With


End Sub

ksp
01-07-2018, 03:50 AM
Thanks so much for making my day...had been at it for days...