I have a macro that is suppose to allow me to enter data into Cells A2, B2,
C2, D2, E2 and F2 of sheet 1. Then have that data exported to the
corresponding cells in a sequential order on sheet 2.
Every time data is entered into the data entry cells on sheet 1 that new
data is to go to the next available, (non-populated cell) in the
corresponding column on sheet 2.
One issue that I am having is if incorrect data is entered into a cell on
sheet 1. You will receive the incorrect data type has been entered message
through Data Validation. However, when the error is corrected the original
incorrect data type remains in the sheet 2 cell.
Also, when data is entered in sheet 1 it is suppose to leave the cell on
sheet one blank.
Below is what I am using.
Any and all help would be greatly appreciated.

Thanks,





Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDest As Range
With Target
If .Count = 1 Then
If .Row = 2 Then
With Sheets("Raw Data").Cells(Rows.Count, .Column).End(xlUp)
..Offset(1 + IsEmpty(.Value), 0).Value = Target.Value
End With
End If
End If
End With
If target.Column = 6 Then
Cells(target.Row + 1 - 1, "A").Select
End If
End Sub

How about losing the data|validation and do it in your code. If the entry isn't
valid, you can empty the cell and not do the copying.

If the entry is valid, then do your stuff.

Kindlysinful wrote:

I have a macro that is suppose to allow me to enter data into Cells A2, B2,
C2, D2, E2 and F2 of sheet 1. Then have that data exported to the
corresponding cells in a sequential order on sheet 2.
Every time data is entered into the data entry cells on sheet 1 that new
data is to go to the next available, (non-populated cell) in the
corresponding column on sheet 2.
One issue that I am having is if incorrect data is entered into a cell on
sheet 1. You will receive the incorrect data type has been entered message
through Data Validation. However, when the error is corrected the original
incorrect data type remains in the sheet 2 cell.
Also, when data is entered in sheet 1 it is suppose to leave the cell on
sheet one blank.
Below is what I am using.
Any and all help would be greatly appreciated.

Thanks,



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDest As Range
With Target
If .Count = 1 Then
If .Row = 2 Then
With Sheets("Raw Data").Cells(Rows.Count, .Column).End(xlUp)
.Offset(1 + IsEmpty(.Value), 0).Value = Target.Value
End With
End If
End If
End With
If target.Column = 6 Then
Cells(target.Row + 1 - 1, "A").Select
End If
End Sub

--

Dave Peterson