working with macros: keyword search into a new sheet from my basesheet with 1000 entries.

[Aishwerya]

hi..i am very new to excel and with macros as well..but i want to know if I am having a spreadsheet with around more than 1000 products in a single column and a corresponding column with another value and i want to create a list of 10 products from the base sheet into a new worksheet by only typing the keywords in the new sheet. Then how can that be done ?
It should pull the product name as well as the corresponding value present in the base sheet and should work with every new sheet opened.

 

[JLGWhiz]

You need to use a specific range to type in your entries and it should be the same range on each new sheet. I am not quite sure what this means

should work with every new sheet opened.

, bit if you want to copy from a source workbook to sheets in a new work book, then in the new workbook the code below installed in the ThisWorkbook code module should work. Assume your base workbook name is "Source.xlsx) and is in the same directory as the destination workbook and both workbooks are open. Assume the entry range is Column A of the destination sheet and the source range is Columns A and B of the source sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim sh1 As Worksheet, fn As Range
Set sh1 = Workbooks("Source.xlsx").Sheets("Sheet1") 'This would be the sheet holding the source list.  Use actual workbook and sheet name.
If Not Intersect(Target, sh.Range("A:A")) Is Nothing Then
 set fn = sh1.Range("A:A").Find(Target.Value, , xlValues, xlWhole)
  If Not fn Is Nothing Then
   fn.Resize(1, 2).Copy Target
  End If
End If
Application.EnableEvents = True
End Sub