Paste changing cell contents to consecutive cells in a column
 

I would like for the contents of a cell (ie: A21) to post to a cell in
another column (ie: D1). However, when the contents of A21 change I would
like for the new figure to post to the next available cell in column D (D2).
I want that process to continue in consecutive cells as the value of A21
continually changes.

Column A is a column in which I am adding figures. A21 is the sum. D would
be a record of all of those sums.

Paste changing cell contents to consecutive cells in a column
 

Easy to do, but I need to know if A21 changes because data is entered into it
or because it has a formula.
--
Gary''s Student - gsnu200742


"meggy" wrote:

I would like for the contents of a cell (ie: A21) to post to a cell in
another column (ie: D1). However, when the contents of A21 change I would
like for the new figure to post to the next available cell in column D (D2).
I want that process to continue in consecutive cells as the value of A21
continually changes.

Column A is a column in which I am adding figures. A21 is the sum. D would
be a record of all of those sums.

Paste changing cell contents to consecutive cells in a column
 

Put the following macro in worksheet code:

Private Sub Worksheet_Calculate()
n = Cells(Rows.Count, "D").End(xlUp).Row
v1 = Range("A21").Value
v2 = Cells(n, "D").Value
If v1 = v2 Then Exit Sub
Cells(n + 1, "D").Value = v1
End Sub

The recording will start in cell D2 and work downward.

REMEMBER: worksheet code, not a standard module.
--
Gary''s Student - gsnu200742


"meggy" wrote:

I would like for the contents of a cell (ie: A21) to post to a cell in
another column (ie: D1). However, when the contents of A21 change I would
like for the new figure to post to the next available cell in column D (D2).
I want that process to continue in consecutive cells as the value of A21
continually changes.

Column A is a column in which I am adding figures. A21 is the sum. D would
be a record of all of those sums.

Paste changing cell contents to consecutive cells in a column
 

I assume A21 has a SUM formula like =SUM(A1:A20)

This sheet event code will place the latest calculation in column D whenever the
SUM in A21 changes.

Starts entering the numbers in D2 and works its way down.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A21")
If .Value < "" Then
ActiveSheet.Cells(Rows.Count, 4).End(xlUp) _
.Offset(1, 0).Value = Me.Range("A21").Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 4 Sep 2007 10:12:02 -0700, meggy
wrote:

I would like for the contents of a cell (ie: A21) to post to a cell in
another column (ie: D1). However, when the contents of A21 change I would
like for the new figure to post to the next available cell in column D (D2).
I want that process to continue in consecutive cells as the value of A21
continually changes.

Column A is a column in which I am adding figures. A21 is the sum. D would
be a record of all of those sums.