CODEWITHSUNDEEP
vbaexcel
Preston Richardson
Preston Richardson

Reputation: 21

Copy each cell in a range to multi-cell range

I'm trying to copy a range of cells.

I have cells D1:D10 and need to copy D1 to A1:A20, then move to D2 and copy it A21:A40, and so on.

I copied D1 to A1:A20.

How do I move down to the next cell. ("CountofResponses") is equal to 20.

Private Sub CommandButton1_Click()

Dim i As Integer
Range("D1").Select
Selection.Copy
For i = 1 To Range("CountofResponses")
    Range("A" & 1 + i).Select
    ActiveSheet.Paste
Next i

End Sub

Upvotes: 0

Views: 3171

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9897

Rewriting this as I misunderstood the first part. A VBA function called OFFSET is what you're looking for. There are more efficient ways of accomplishing your overall objective that doesn't use copy/paste, but for the sake of an exact answer... here you go:

    Private Sub CommandButton1_Click()
    Dim i As Long
    For i = 1 To Range("CountofResponses").Value
        Range("D1").Offset(i-1,0).Copy Range("A" & 1 + i)
       'or you could use Range("A1").offset(i-1,0).Select
       
    Next i
    End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

Try this

Private Sub CommandButton1_Click()

Dim r As Range

For Each r In Range("D1:D10")
    Range("A" & 1 + (r.Row - 1) * 20).Resize(Range("CountofResponses")).Value = r.Value
Next i

End Sub

Upvotes: 3

Related Questions