Automatically Copy Data from One Group of Cells to Another

[Alan Auerbach]

I have five columns of data on two different sheets in the same workbook. One
set of columns is sorted in ascending date order the other in descending date
order. When I enter data into the last row of Sheet 1, I need the data in
that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D
and E in a newly inserted row 14. Is this possible with the use of a macro? I
can find the last cell in Sheet 1, but then need to go up one row and back to
column A. I am having difficulty with that.

Thanks is advance for any assistance offered!

/s/ Alan Auerbach

 

[Gary''s Student]

If you want A,B,C,D to go into A,B,C,D,E
what goes into E?

 

[JE McGimpsey]

If I understand you correctly, one way:

Put this in your Sheet1 worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rSource As Range
With Target
If Not Intersect(.Cells, Cells(Rows.Count,1).End( _
xlUp).Resize(1, 4)) Is Nothing Then
Set rSource = Cells(.Row, 1).Resize(1, 4)
If Application.CountA(rSource) = 4 Then
With Sheets("Sheet2").Cells(14, 1).EntireRow
.Insert
With .Offset(-1, 0)
.Cells(1).Value = rSource(1).Value
.Cells(3).Resize(1, 3).Value = _
rSource.Offset(0, 1).Resize(1, 3).Value
End With
End With
End If
End If
End With
End Sub

This will insert the values from the last row into sheet2, (inserted)
row 14 when the input row's cells A are filled.

 

[JE McGimpsey]

Per the OP's problem statement:

A -> A
B -> C
C -> D
D -> E