Multiple data entry cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kindlysinful - 05 May 2008 03:36 GMT

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

Any help???

Reply to this Message

JE McGimpsey - 06 May 2008 15:25 GMT

> Hello,
>
[quoted text clipped - 18 lines]
> 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

Reply to this Message

Kindlysinful - 07 May 2008 06:34 GMT

Thank you very much, that worked great.

> > Hello,
> >
[quoted text clipped - 33 lines]
> End With
> End Sub

Reply to this Message

Mac Forum / Applications / Excel / May 2008

Multiple data entry cells