worksheet code

R

Rod

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.
 
T

Tyro

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

Rod

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
 
G

Gord Dibben

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
 
J

Joe_Germany

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






- Show quoted text -
Click to expand...

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
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
G

Gord Dibben

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
 
R

Rod

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

Rod

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.
 
R

Rod

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.
 
G

Gord Dibben

After first value is placed in A2, select A1 and edit>delete>shift up.

Now you're OK to go.


Gord
 
Top