search and copy to another sheet

[arnab07111]

Hi,
I am attaching an excel workbook where there is a search option in cell h15,sheet Kol data.The logic is if I give an item code like(MW071030) in the search field and press search it will search for the related item in sheet "KOL DATA" and copy the details from the sheet and paste it in sheet "Kol WMS",row 16.Please advice the VBA code.

The password for opening the macro - 9D5MLB

[mohanvijay]

Hai use this code

i attached with code



Dim holdwms(1 To 500), holdpal(1 To 500), holddes(1 To 500), holdqty(1 To 500) As Variant
Dim repeat, repeat1 As Integer

repeat = 1
repeat1 = 1
mat_code = Sheets(1).OLEObjects("Textbox1").Object.Value

For i = 5 To 10000
matcode = Sheets("KOL DATA").Cells(i, 4).Value
If mat_code = matcode Then
holdwms(repeat) = Sheets("KOL DATA").Cells(i, 2).Value
holdpal(repeat) = Sheets("KOL DATA").Cells(i, 3).Value
holddes(repeat) = Sheets("KOL DATA").Cells(i, 5).Value
holdqty(repeat) = Sheets("KOL DATA").Cells(i, 6).Value
repeat = repeat + 1
End If

Next i

If repeat = 1 Then
MsgBox "No Items Found"
Exit Sub
End If

For ii = 16 To 16 + (repeat - 2)
Sheets("KOL WMS").Cells(ii, 3).Value = holdwms(repeat1)
Sheets("KOL WMS").Cells(ii, 4).Value = holdpal(repeat1)
Sheets("KOL WMS").Cells(ii, 6).Value = holddes(repeat1)
Sheets("KOL WMS").Cells(ii, 7).Value = holdqty(repeat1)
Sheets("KOL WMS").Cells(ii, 5).Value = mat_code
repeat1 = repeat1 + 1
Next ii

[mdmackillop]

Sub Macro1()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("KOL DATA")
Set ws2 = Sheets("KOL WMS")
With ws1
If .FilterMode Then .ShowAllData
.Range("B4:F4").Copy .Range("J1")
.Range("L2").Value = ws2.OLEObjects("TextBox1").Object.Value
.Range("B4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
.Range("J1:N2"), Unique:=False
.Range("B4").CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Copy
ws2.Range("C16").PasteSpecial xlPasteValues
.ShowAllData
.Range("J1:N2").Clear
End With
ws2.Range("C14").Select
End Sub

[arnab07111]

thanks a lot