selecting relative ranges

[John Br]

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

 

[sbitaxi]

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 listand
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

I am assuming that you want to drop the data in column Q after the
last row containing data. This will copy the contents of the active
cell to the end of column Q

Sub copy4()
Dim CopyTo As Range
Dim CopyFrom As Range
Set CopyTo = Range("Q1").End(xlUp)
Set CopyFrom = ActiveCell
CopyTffset(1, 0).Value = CopyFrom.Value

End Sub

 

[John Br]

I am assuming that you want to drop the data in column Q after the
last row containing data. This will copy the contents of the active
cell to the end of column Q

Sub copy4()
Dim CopyTo As Range
Dim CopyFrom As Range
Set CopyTo = Range("Q1").End(xlUp)
Set CopyFrom = ActiveCell
CopyTffset(1, 0).Value = CopyFrom.Value

End Sub

Thanks, but it does the same thing as my macro, which is: it overwrites the
previous entry rather than append the second item after the first.

 

[JMB]

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