thank you.. but the first value is placing in cell A2 of sheet2.. I need from
the cell A1 of sheet2.. I don't know VBA .. please help me.

"Joe_Germany" wrote:

On Jan 4, 11:14 am, Gord Dibben <gorddibbATshawDOTca wrote:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
With Target
If .Value < "" Then
Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab of Sheet1 and "View
Code".

Copy/paste the code into that sheet.

First value entered in Sheet1!A1 will go into Sheet2!A2 then A3, A4 etc.

Gord Dibben MS Excel MVP



On Thu, 3 Jan 2008 19:56:00 -0800, Rod wrote:
Hi
Happy New Year to all.
I need a code that each time I enter value in cell A1 of sheet1 should be
placed in column A of sheet2.. I mean if I enter values in sheet1!A1 one
after another it should be placed in sheet2!A1 and second value in sheet2!A2
and so on.

thanks in advance.- Hide quoted text -

- Show quoted text -

Hi Gord,

I have a doubt about the following line...
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
is that mean that it will copy only for Range A1?? I dont know!

I just tried to modify that code like follows...

'******************************

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Dim Row, Col As Integer

If Target = "" Then Exit Sub
Application.EnableEvents = False

Row = Target.Row
Col = Target.Column
Sheets("Sheet2").Cells(Row, Col).Value = Target.Value

stoppit:
Application.EnableEvents = True
End Sub

'******************

I didnt check this code...

Regards,
Joe