I had 2 Sheets in a excel file, Sheet1 & sheet2. I feed records in sheet1 having certain format. I want the records from sheet1 to get copied by using VBA to the sheet2's specific rows / columns.

Here in sheet2, what i need is when i enter the record no. of sheet1 in specific row / column of sheet2, that record of sheet1 should get copied into the sheet2.

Please help me for the same.



Yogesh

__________________
Yogesh

This is pretty straight forward. This example assumes the rows are the same from sheet 1 to sheet 2.

Copy this code into Sheet2 (The sheet the data is to be on):
-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If it's column A and not row 1, finds the value from sheet 1 and copies it to this sheet
  Dim oFind As Range
  If Target.Column = 1 And Target.Row > 1 Then
    Set oFind = Worksheets("Sheet1").Range("A:A").Find(Target.Valu e, LookIn:=xlValues) 'Source sheet name is Sheet1
    If Not oFind Is Nothing Then 'Does below copy if a corresponding value found on Sheet1 in column A
      oFind.EntireRow.Copy Destination:=Worksheets("Sheet2").Range(Target.Add ress(False, False)) 'Target is Sheet2
    End If
  End If

End Sub
-----------------------------------------------------------------------------

Hope this helps.

P.S. Also of note is that the above code is set to skip value changes for row 1 which I use for headings. If you have 2 heading rows and your detail row starts on row 3 (Where you want data to start copying if found) thhen change the if/then to be:

If Target.Column = 1 and Target.Row > 2 'Note that if your compare data is in column C then column would = 3 instead of 1

Just a hopefully helpful clarification