How can I copy cell entry to next empty row down in VBA?

Question

So ive created a worksheet to monitor stock positions. I would like to log data from every closed stock position with a button. The first command in my string is to copy the stocks name from within the same worksheet. I Like to know how to do this from within the same worksheet and syntax to do it from another worksheet within the same document. Thank you.

This is the code I run and it returns a Run-Time error '1004' Range of Object Global failed.

Sub LogPosition()
Dim rngStart As Range
Set rngStart = ActiveCell
Sheets("Charts").Select
ActiveSheet.Range("D5").Select
Selection.Copy
Range("D5" & Rows.Count).End(xlUp).Offset(1, 
0).PasteSpecial xlPasteValues
rngStart.Select
End Sub

Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba. You can't select a cell unless its sheet is already active. — SJR, Mar 12 '20 at 19:54

score 1 · Accepted Answer · answered Mar 12 '20 at 19:56

Change your line Range("D5" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues in Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues.

But, no need to select anything to obtain the same result. This line will replace all your code:

Range("D" & Rows.Count).End(xlUp).Offset(1, 0).value = ActiveSheet.Range("D5").value

And the next line will paste the "D5' value in another sheet:

Sheets("Charts").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).value = ActiveSheet.Range("D5").value

That did it! thanks a mill. Something so simple taking forever to figure out. lol — HC_Tech, Mar 12 '20 at 20:02

How can I copy cell entry to next empty row down in VBA?

1 Answers1