command button copy and paste cell l9 from sheet 1 to next available cell in column e

[joespencerno1]

hi everyone, I have a problem and hoping it's an easy fix, I want to copy a value of cell ("L9") on sheet1 and paste the value in the next empty cell in column E and for this to keep doing so as the value in sheet1 L9 will change all the time and need to record the values. so I created a command button so this will do it on a button click but im not sure how to write the code into VBA. Can anyone help please
many thanks
Joe

[mgs73]

Try this in the first sheets module, I think it will do what you want:

Please Login or Register  to view this content.

[joespencerno1]

will this work on a command button? sorry im a novice when it comes to vba
thanks
joe

[Logit]

.

Paste the following in a regular module :

Please Login or Register  to view this content.

Connect the Command Button to this macro.

[joespencerno1]

ok I just tried that but its not putting the value into the next cell down the column, its just adding the total to the same cell. and the reason for the command button is so I can control when it copied and pasted, sorry if I wasn't clear
thanks
joe

[joespencerno1]

its ok I figured it out in the end thankyou for your help and here is how I did if you was curious

Private Sub CommandButton5_Click()
Dim AdCash As Range
With Sheets("Daily Reports")
Set AdCash = .Cells(.Rows.Count, 6).End(xlUp).Offset(1, 0)
End With
Sheet1.Range("L9").Copy
AdCash.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set AdCash = Nothing
Sheets("Daily Reports").Select
ActiveCell.Offset(1, 0).Select
Sheet1.Select

End Sub

thanks again
Joe

[jolivanes]

Please put your code between code tags.
Or

Please Login or Register  to view this content.

[Logit]

ok I just tried that but its not putting the value into the next cell down the column, its just adding the total to the same cell. and the reason for the command button is so I can control when it copied and pasted, sorry if I wasn't clear
thanks
joe

Strange, it's working as designed here.

[jolivanes]

Instead of the 11 lines from Post #6, did you try the one liner in Post #7?