Hi I want to create a button in the spreadsheet so it updates a value in one
cell and moves it to another specified cell for example in A1 the number is 5
I want that copied to say C5, and then the next number I put in A1, when I
press that button again and it inserts it in C6 and so on.

Jonathan

Without a button by using Event Code.

Assumes C4 has an entry of some sort and remainder of Column C is blank.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value < "" Then
If IsNumeric(Target.Value) Then
ActiveSheet.Cells(Rows.Count, 3).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
End If
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.


Gord Dibben MS Excel MVP


On Tue, 20 Mar 2007 15:00:38 -0700, JonathanW
wrote:

Hi I want to create a button in the spreadsheet so it updates a value in one
cell and moves it to another specified cell for example in A1 the number is 5
I want that copied to say C5, and then the next number I put in A1, when I
press that button again and it inserts it in C6 and so on.

With a button from the Forms toolbar:

Sub Cell_Mover()
Dim strInput As String
strInput = Range("A1").Value
Range("C5").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = strInput
End Sub

"JonathanW" wrote:

Hi I want to create a button in the spreadsheet so it updates a value in one
cell and moves it to another specified cell for example in A1 the number is 5
I want that copied to say C5, and then the next number I put in A1, when I
press that button again and it inserts it in C6 and so on.