Discussion:
Copy (“A2:A26”) paste, offset one cell, do it again. Loop 256 tim
(too old to reply)
RyGuy
2009-10-22 18:54:01 UTC
Permalink
I am trying to figure out a way to take a range (“A2:A26”) and copy/paste it
into range (“D55:D79”) then offset one cell down, then do it again, so
(“A2:A26”) copy/paste to (“D81:D105”) and so on and so forth. I want it to
run 256 times.

I am looking for a sample of code that I thought I had somewhere in my
library, but can’t seem to find it now. Any ideas?

Thanks,
Ryan--
Mike H
2009-10-22 19:08:01 UTC
Permalink
Hi,

Try this

Sub Versive()
Range("A2:A26").Copy
For x = 55 To 1430 Step 26
Cells(x, 4).PasteSpecial
Next
End Sub

Mike
Post by RyGuy
I am trying to figure out a way to take a range (“A2:A26”) and copy/paste it
into range (“D55:D79”) then offset one cell down, then do it again, so
(“A2:A26”) copy/paste to (“D81:D105”) and so on and so forth. I want it to
run 256 times.
I am looking for a sample of code that I thought I had somewhere in my
library, but can’t seem to find it now. Any ideas?
Thanks,
Ryan--
RyGuy
2009-10-22 19:12:03 UTC
Permalink
Thanks! Very cool. This macro worked pretty well too:

Sub CopyDown()
Dim lstRw As Long
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Do Until lstRw >= 7000
ActiveCell.Resize(26, 1).Copy ActiveCell.Offset(26, 0)
lstRw = lstRw + 2
Range("A" & lstRw - 9).Activate
Loop
End Sub

It was off by one row, so I inserted one cell down, and everything lined up
nicely.

Hope this helps others.


Thanks again Mike!
Ryan--
Post by Mike H
Hi,
Try this
Sub Versive()
Range("A2:A26").Copy
For x = 55 To 1430 Step 26
Cells(x, 4).PasteSpecial
Next
End Sub
Mike
Post by RyGuy
I am trying to figure out a way to take a range (“A2:A26”) and copy/paste it
into range (“D55:D79”) then offset one cell down, then do it again, so
(“A2:A26”) copy/paste to (“D81:D105”) and so on and so forth. I want it to
run 256 times.
I am looking for a sample of code that I thought I had somewhere in my
library, but can’t seem to find it now. Any ideas?
Thanks,
Ryan--
Mike H
2009-10-22 19:25:01 UTC
Permalink
Glad I could help and thanks for the feedback
Post by RyGuy
Sub CopyDown()
Dim lstRw As Long
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Do Until lstRw >= 7000
ActiveCell.Resize(26, 1).Copy ActiveCell.Offset(26, 0)
lstRw = lstRw + 2
Range("A" & lstRw - 9).Activate
Loop
End Sub
It was off by one row, so I inserted one cell down, and everything lined up
nicely.
Hope this helps others.
Thanks again Mike!
Ryan--
Post by Mike H
Hi,
Try this
Sub Versive()
Range("A2:A26").Copy
For x = 55 To 1430 Step 26
Cells(x, 4).PasteSpecial
Next
End Sub
Mike
Post by RyGuy
I am trying to figure out a way to take a range (“A2:A26”) and copy/paste it
into range (“D55:D79”) then offset one cell down, then do it again, so
(“A2:A26”) copy/paste to (“D81:D105”) and so on and so forth. I want it to
run 256 times.
I am looking for a sample of code that I thought I had somewhere in my
library, but can’t seem to find it now. Any ideas?
Thanks,
Ryan--
Mike H
2009-10-22 19:21:02 UTC
Permalink
Got the end value for the loop wroong, try this

For x = 55 To 6701 Step 26
Post by Mike H
Hi,
Try this
Sub Versive()
Range("A2:A26").Copy
For x = 55 To 1430 Step 26
Cells(x, 4).PasteSpecial
Next
End Sub
Mike
Post by RyGuy
I am trying to figure out a way to take a range (“A2:A26”) and copy/paste it
into range (“D55:D79”) then offset one cell down, then do it again, so
(“A2:A26”) copy/paste to (“D81:D105”) and so on and so forth. I want it to
run 256 times.
I am looking for a sample of code that I thought I had somewhere in my
library, but can’t seem to find it now. Any ideas?
Thanks,
Ryan--
Loading...