Try:

Sub Copy4()
Selection.Copy Range("Q" & Rows.Count).End(xlUp)(2, 1).Resize(4, 1)
End Sub

Assuming your list will not fill all the way to the bottom of the
spreadsheet it goes to the last row of Column Q and Ctrl-Up to find the last
cell with data in it.

One thing you might change is Selection.Copy to Range("A1").Copy (or
whatever the cell address is - or name the cell and use
Range("NamedRange").Copy).

Chip has a brief discussion on referencing cells in ranges here
http://www.cpearson.com/Excel/cells.htm



"John Br" wrote:

I'm new to macros and I recorded the following macro in an attempt to learn
how to append items to an ever growing list. I am attempting append 4 copies
of a cell to the end of a column list. I thought I could get to the first
blank cell at the end of the list by going to a blank cell above the list and
selecting "End, Down" twice and then Down. This works fine the first time,
but when I repeat it, the last entry is overwritten. I don't know how to get
the range statement to not be Q8:Q11, but rather a relative address.

Any help would be appreciated.

Sub Copy4()
Selection.Copy
Application.Goto Reference:="R1C17"
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("Q8:Q11").Select
ActiveSheet.Paste
End Sub