automatically adding data to list

[NOTTNICK]

I can't seem to find any reference to this so maybe someone can point
me in the right direction.
I have a spreadsheet I have produced and would like to have the
facility so that if I enter a figure in one cell, then the value is
automatically appended to the bottom of another list.

ie. New data added to cell 1A automatically is copied to the first
blank cell in the column 2B or 3B or 4B etc.

This could work backwards for me too if it is easier

ie. Data entered at the bottom of a list (2B...3B....4B....etc)
overwrites the remote copy (1A) of the value in the cell above.

Is this something simple to do?
Does this make sense?

Thanks

 

[Dave Peterson]

I've never been a fan of these kinds of automatic things. Too many things can
go wrong (typing errors for example). I would use a button near A1 that would
have to be clicked for the value to be copied to the bottom of column B.

But if you want, you can use a worksheet event that will fire whenever you
change something on the page. It'll look to see what you changed and determine
whether something should be done.

If you want to try...

Right click on the worksheet tab that should have this behavior and select View
Code. Then paste this into the newly opened code window.

Then back to excel and type some things into A1. As soon as you finish your
entry (enter key??), look at column B.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim NextCell As Range

If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
End If

With Me
Set NextCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(NextCell) Then
'stay here (in B1)
Else
'move down a row
Set NextCell = NextCell.Offset(1, 0)
End If
End With

On Error GoTo ErrHandler:
Application.EnableEvents = False
NextCell.Value = Me.Range("a1").Value

ErrHandler:
Application.EnableEvents = True
On Error GoTo 0

End Sub

 

[NOTTNICK]

I've never been a fan of these kinds of automatic things.  Too many things can
go wrong (typing errors for example).  I would use a button near A1 that would
have to be clicked for the value to be copied to the bottom of column B.

But if you want, you can use a worksheet event that will fire whenever you
change something on the page.  It'll look to see what you changed and determine
whether something should be done.

If you want to try...

Right click on the worksheet tab that should have this behavior and select View
Code.  Then paste this into the newly opened code window.

Then back to excel and type some things into A1.  As soon as you finishyour
entry (enter key??), look at column B.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NextCell As Range

    If Intersect(Target, Me.Range("A1")) Is Nothing Then
        Exit Sub
    End If

    With Me
        Set NextCell = .Cells(.Rows.Count, "B").End(xlUp)
        If IsEmpty(NextCell) Then
            'stay here (in B1)
        Else
            'move down a row
            Set NextCell = NextCell.Offset(1, 0)
        End If
    End With

    On Error GoTo ErrHandler:
    Application.EnableEvents = False
    NextCell.Value = Me.Range("a1").Value

ErrHandler:
    Application.EnableEvents = True
    On Error GoTo 0

End Sub











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Wow!
Exactly what I wanted.
It's a fairly straightforward spreadsheet so I altered the cell
references and it works brilliantly.
Thanks

Feeling very stupid though - use excel all the time for work and play
and realise that I referenced the cells backwards earlier!! 1A instead
of A1 !!! What am I thinking? I teach ICT too!
A long day.

Nick

 

[NOTTNICK]

Wow!
Exactly what I wanted.
It's a fairly straightforward spreadsheet so I altered the cell
references and it works brilliantly.
Thanks

Feeling very stupid though - use excel all the time for work and play
and realise that I referenced the cells backwards earlier!! 1A instead
of A1 !!! What am I thinking? I teach ICT too!
A long day.

Nick

- Hide quoted text -

- Show quoted text -

Being a bit greedy now, but as it works so well.

I tried adding another cell reference too, but it is obviously more
complex than I thought.

I have amended the code you gave me to suit the spreadsheet I have -
Cell D15 updates column N. This works just right.

Ideally (now I see how well it works) I'd like D14 to update column O
as well.

A suggestion would be brilliant.

Thanks

Nick