In article <78BC6AAB-25C7-4648-BEF5-2729793F8402@[EMAIL PROTECTED]
>,
Kindlysinful <Kindlysinful@[EMAIL PROTECTED]
> wrote:
> Hello,
>
> I am using the following to enter data into cell A2 sheet 1. Then have
that
> data transfered to sheet 2 the next available cell in column A.
> (a2,a3,a4,a5,a6.... and so on)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address <> "$A$2" Then Exit Sub
> If Target.Cells.Count > 1 Then Exit Sub
> If Target.Value = "" Then Exit Sub
> Application.ScreenUpdating = False
> Target.Copy Destination:=Sheets("Sheet2").Range("A" &
> Rows.Count).End(xlUp).Offset(1, 0)
> Target.Value = ""
> Application.ScreenUpdating = True
> End Sub
>
> What I need to do is be able to enter data into A2, B2, C2, D2, E2....
and
> have that data sent to the next available cell in it's respective column
on
> sheet 2.
> If I enter into B2 sheet 1 that data will goto Sheet 2 B2, then the
next
> time I enter data into Sheet 1 B2 that data will goto Sheet 2 B3 and so
on....
One way:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rDest As Range
With Target
If .Count = 1 Then
If .Row = 2 Then
With Sheets("Sheet2").Cells(Rows.Count, .Column).End(xlUp)
.Offset(1 + IsEmpty(.Value), 0).Value = Target.Value
End With
End If
End If
End With
End Sub
|
