copy a value and paste the same until it reaches a specific cell

Question

I would like to copy the value present in the B1 to B20 and paste until B40000

I tried

 Range(Selection, Selection.End(xlDown)).Select
 Selection.Copy
 Range("A2").Select
 Selection.End(xlDown).Select
 Range("B21").Select
 ActiveSheet.Paste
 Range("A21").Select
 Selection.End(xlDown).Select
Range("B40").Select

I would like to change the range which runs until B40000

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Dec 16 '19 at 09:37
What about `Range("B2:B20").Copy Destination:=Range("B21:B40000")` — Pᴇʜ, Dec 16 '19 at 09:39
This code edited into Range("B2:B20").Copy Range("B21:B40000").PasteSpecial it is not working — Roguen, Dec 16 '19 at 09:49
*"is not working"* is a completely useless error description. — Pᴇʜ, Dec 16 '19 at 10:42

score 1 · Accepted Answer · answered Dec 16 '19 at 10:44

Ensure that the size of the destination is a multiple of the sources size to copy properly:

Option Explicit

Public Sub CopyRangeDown()
    Dim Source As Range
    Set Source = Range("B2:B20")

    Source.Copy
    Range("B21").Resize(RowSize:=Source.Rows.Count * CLng(40000 / Source.Rows.Count)).PasteSpecial
End Sub

copy a value and paste the same until it reaches a specific cell

1 Answers1