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.

In A1 on sheet2 put =sheet1!A1 and drag the formula down the column for as
many rows as you need.

"Rod" wrote in message
...
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.

not exactly... I mean if I have 20 numbers(say) first time I enter first
value in Sheet1!A1 that should be placed in Sheet2!A1 and next time I enter
the second value in Sheet1!A1 that should be placed in Sheet2!A2 and so
on.........

thanks for response


"Tyro" wrote:

In A1 on sheet2 put =sheet1!A1 and drag the formula down the column for as
many rows as you need.

"Rod" wrote in message
...
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.

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.

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

I think that Gord may have meant to put the

Application.EnableEvents = False

line after the

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

line otherwise if the *If* line causes the sub to terminate then Events will
still be disabled.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

Thanks Sandy.

Wouldn't want to exit without re-enabling events.

Revised version......................

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


Gord

On Fri, 4 Jan 2008 11:38:11 -0000, "Sandy Mann"
wrote:

I think that Gord may have meant to put the

Application.EnableEvents = False

line after the

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

line otherwise if the *If* line causes the sub to terminate then Events will
still be disabled.

thank you all... but the first value is placed in sheet2!A2 , I need from
cell A1 in sheet2.. please help me

"Gord Dibben" wrote:

Thanks Sandy.

Wouldn't want to exit without re-enabling events.

Revised version......................

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


Gord

On Fri, 4 Jan 2008 11:38:11 -0000, "Sandy Mann"
wrote:

I think that Gord may have meant to put the

Application.EnableEvents = False

line after the

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

line otherwise if the *If* line causes the sub to terminate then Events will
still be disabled.

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

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.

"Sandy Mann" wrote:

I think that Gord may have meant to put the

Application.EnableEvents = False

line after the

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

line otherwise if the *If* line causes the sub to terminate then Events will
still be disabled.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

After first value is placed in A2, select A1 and editdeleteshift up.

Now you're OK to go.


Gord
On Fri, 4 Jan 2008 20:33:00 -0800, Rod wrote:

thank you all... but the first value is placed in sheet2!A2 , I need from
cell A1 in sheet2.. please help me

"Gord Dibben" wrote:

Thanks Sandy.

Wouldn't want to exit without re-enabling events.

Revised version......................

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


Gord

On Fri, 4 Jan 2008 11:38:11 -0000, "Sandy Mann"
wrote:

I think that Gord may have meant to put the

Application.EnableEvents = False

line after the

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

line otherwise if the *If* line causes the sub to terminate then Events will
still be disabled.

thank you

"Gord Dibben" wrote:

After first value is placed in A2, select A1 and editdeleteshift up.

Now you're OK to go.


Gord
On Fri, 4 Jan 2008 20:33:00 -0800, Rod wrote:

thank you all... but the first value is placed in sheet2!A2 , I need from
cell A1 in sheet2.. please help me

"Gord Dibben" wrote:

Thanks Sandy.

Wouldn't want to exit without re-enabling events.

Revised version......................

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


Gord

On Fri, 4 Jan 2008 11:38:11 -0000, "Sandy Mann"
wrote:

I think that Gord may have meant to put the

Application.EnableEvents = False

line after the

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

line otherwise if the *If* line causes the sub to terminate then Events will
still be disabled.