selecting relative ranges
 

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

selecting relative ranges
 

On Aug 14, 4:04*pm, John Br <John
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

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
CopyTo.Offset(1, 0).Value = CopyFrom.Value

End Sub

selecting relative ranges
 

" wrote:

On Aug 14, 4:04 pm, John Br <John
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

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
CopyTo.Offset(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.

selecting relative ranges
 

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