I am trying to create a macro that will take values that change and
paste them one line below where the previous days numbers where
pasted. So if A1 is a ten I need it to paste in A2. Then the next
day A1 may be a 15 and I need it to paste in A3. I have tried many
things and it always copies the cell above instead of A1. Thanks in
advance.

Hi,

Try this, it pastes any value entered in A1 into the empty cell at the
bottom of the data list in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
lastrowcola = Range("A65536").End(xlUp).Row
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Range("A1").Select
Selection.Copy
Cells(lastrowcola + 1, 1).Select
ActiveSheet.Paste
End If
End Sub

Mikw

"Barnej75" wrote:

I am trying to create a macro that will take values that change and
paste them one line below where the previous days numbers where
pasted. So if A1 is a ten I need it to paste in A2. Then the next
day A1 may be a 15 and I need it to paste in A3. I have tried many
things and it always copies the cell above instead of A1. Thanks in
advance.

Mike,
I was trying to solve this for OP and was using change event code
with Line 2 = Application.Undo ' thinking whatever was entered last(before)
had not yet been copied down to the Bottom. If that were the case, could you
kindly show how this would be acheived?
TIA,
Jim

"Mike H" wrote:

Hi,

Try this, it pastes any value entered in A1 into the empty cell at the
bottom of the data list in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
lastrowcola = Range("A65536").End(xlUp).Row
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Range("A1").Select
Selection.Copy
Cells(lastrowcola + 1, 1).Select
ActiveSheet.Paste
End If
End Sub

Mikw

"Barnej75" wrote:

I am trying to create a macro that will take values that change and
paste them one line below where the previous days numbers where
pasted. So if A1 is a ten I need it to paste in A2. Then the next
day A1 may be a 15 and I need it to paste in A3. I have tried many
things and it always copies the cell above instead of A1. Thanks in
advance.

You'd use Application.Undo if you wanted to retrieve the value that was in A1.
But it sounds like you want to use A1 as an input cell and then just move things
to the bottom of that column.

If that's the case, you could use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestCell As Range
Dim NewVal As Variant

With Target
If .Cells.Count 1 Then Exit Sub
If .Value = "" Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

NewVal = .Range("a1").Value
Set DestCell _
= .Parent.Cells(.Parent.Rows.Count, "A").End(xlUp).Offset(1, 0)

Application.EnableEvents = False
DestCell.Value = NewVal

'clear for next time???
.Value = ""

End With

ErrHandler:
Application.EnableEvents = True

End Sub

If you really wanted to keep that existing value, you could use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestCell As Range
Dim NewVal As Variant

With Target
If .Cells.Count 1 Then Exit Sub
If .Value = "" Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

NewVal = .Range("a1").Value
Set DestCell _
= .Parent.Cells(.Parent.Rows.Count, "A").End(xlUp).Offset(1, 0)

With Application
.EnableEvents = False
.Undo
End With

DestCell.Value = NewVal

End With

ErrHandler:
Application.EnableEvents = True

End Sub

JMay wrote:

Mike,
I was trying to solve this for OP and was using change event code
with Line 2 = Application.Undo ' thinking whatever was entered last(before)
had not yet been copied down to the Bottom. If that were the case, could you
kindly show how this would be acheived?
TIA,
Jim

"Mike H" wrote:

Hi,

Try this, it pastes any value entered in A1 into the empty cell at the
bottom of the data list in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
lastrowcola = Range("A65536").End(xlUp).Row
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Range("A1").Select
Selection.Copy
Cells(lastrowcola + 1, 1).Select
ActiveSheet.Paste
End If
End Sub

Mikw

"Barnej75" wrote:

I am trying to create a macro that will take values that change and
paste them one line below where the previous days numbers where
pasted. So if A1 is a ten I need it to paste in A2. Then the next
day A1 may be a 15 and I need it to paste in A3. I have tried many
things and it always copies the cell above instead of A1. Thanks in
advance.



--

Dave Peterson

Thank you for all your help. I ended up using it like this


Sub Macro1()
lastrowcola = Range("A65536").End(xlUp).Row
Range("A1:C1").Select
Selection.Copy
Cells(lastrowcola + 1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

It seems to be doing what I need it to. If you see anything wrong
with how I am doing it please let me know.

Thanks
Jason