ralative range reference

[mikej28621]

I need to be able to copy a cell to a range of cells. I was able to accomplish it by

Selection.Copy
colIndex = ActiveCell.Column
rwIndex = ActiveCell.Row
For rwIndex = 6 To 20
With Worksheets("Sheet1").Cells(rwIndex, colIndex)
Cell(rwIndex, colIndex).Select
ActiveSheet.Paste
End With
Next rwIndex

but I want wondering if there was a way to do it like

Selection.Copy
Range("E7:E16").Select
ActiveSheet.Paste

but using a relative reference rather then the Absolute "E7:E16" reference. I am also considering the R1C1 notation. Any thoughts appreciated.

 

[VBAjedi]

You can specify the destination range immediately after the Copy command (separated by a space). Also, you only need to specify the top-left cell for the paste range. So you can use the Offset function for a relative range:

Selection.Copy Selection.Offset(0, 5)



VBAjedi

 

[mikej28621]

Thanks Jedi. I have looked at that one but I need to copy a single cell to a range of cells.

 

[mikej28621]

I found the answer....

Selection.Copy
ActiveCell.Range("A1:A10").Select
ActiveSheet.Paste

Range becomes a sub-range of the existing cell

for those of you that might need sleep... may is suggest reading

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexvba/html/odc_5709_chap05idx.asp

Thanks

 

[VBAjedi]

Or the one-line version:

Selection.Copy ActiveCell.Range("A1:A10")




VBAjedi