Stack Overflow Asked by Preston Richardson on November 29, 2021
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
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
Answered by SJR on November 29, 2021
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
Answered by pgSystemTester on November 29, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP