I have 2 sheets namelt sheet1 and sheet2. Now i had prepared a report in which there are several columns or fields/headings. The first column is S.No.

In sheet2 i have another report, where some data is same as that of sheet1. I have to re-enter the common data in sheet2 manually. But i want to do it automatically through excel VBA.

The idea is that both sheets has the first columns S.No. If the S.No. typed in Sheet2 Column , then the code should compare the s.no. of both sheets, if they are same then all the data / record of S.No.1 (Sheet1) should get copied into the specific cells of sheet2.

For example: Sheet1 Data


SR.NO. PRODUCT NAME STOCK DATE CONSUMED QTY.
1 Genecel GLM 100 28/03/2007 100
2 Genecel GLM 69.4 28/03/2007 69.4
3 Genecel GLM spl 75 22/02/2007 75
4 Genecel UN spl 50 22/02/2007 50



Sheet2 Data

S.No. Product Inv.No. Inv.Date
1

If the S.No. in sheet2 typed by the user is 2, then the records of S.No.2 (Sheet1) should get copied in the cells of sheet2.





Yogesh

__________________
Yogesh

Here is a simple code for that
Function Snippet_For_Copy(sSearchString)

 If Trim(sSearchString) = "" Then Exit Function

  With sheets(1).Columns("A:A")
        Set rFindCell = .Find(sSearchString, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rFindCell Is Nothing Then
        sheets(1).Rows(rFindCell.Row).EntireRow.Copy _
                        Destination:=Range("A" & ActiveCell.Row)
          End If
   End With

End Function

You can trigger the function using Worksheet_SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If IsNumeric(Target) = False Then Exit Sub
  If Trim(Target) = "" Then Exit Sub

 Application.EnableEvents = False

  Snippet_For_Copy Target.Value

  Application.EnableEvents = True
End Sub

Cheers
Shasur


http://www.vbadud.blogspot.com