macro loop to copy paste hangs

Question

Can someone help me with this loop macro? I want the loop to copy Range("S16:Y16").Select Move down three rows and paste it, then move down three rows and repeat until it reaches 20.

Error is that it goes down three rows then hangs. Any help would be appreciated

Example code

Sub pop1()
    '  Macro
    '
    ' Keyboard Shortcut: Ctrl+f
    '
    Range("S16:Y16").Select
    Selection.Copy
    Range("S19").Select
    ActiveSheet.Paste

    Range("s19:Y19").Select

    For i = 1 To 20
        Selection.Copy
        Range("s19").Offset(3, 0).Select
        ActiveSheet.Paste

        ActiveCell.Offset(3, 0).Select
        ActiveSheet.Paste
    Next i
End Sub

First Things First http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 — Siddharth Rout, Apr 08 '13 at 13:40
Secondly. If you paste in row 19 and move down 3 places, you have already crossed row 20 or is it that you want to paste 20 times? — Siddharth Rout, Apr 08 '13 at 13:47
Has any of the answers answered your question? If so please help other users by marking it as the answer, see [about]. — glh, Apr 11 '13 at 10:37

Siddharth Rout · Answer 1 · 2013-04-08T13:56:04.840

2

If you want to paste 20 times after row 19 then try this

Sub pop1()
    Dim ws As Worksheet
    Dim r As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        r = 19

        For i = 1 To 21
            .Range("S16:Y16").Copy .Range("S" & r)
            r = r + 3
        Next i
    End With
End Sub

EDIT

The above will paste values and if you want to paste it with all format then do this

Sub pop1()
    Dim ws As Worksheet
    Dim r As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        r = 19

        For i = 1 To 21
            .Range("S16:Y16").Copy
            .Range("S" & r).PasteSpecial xlPasteAll
            r = r + 3
        Next i
    End With
End Sub

edited Apr 08 '13 at 13:56

answered Apr 08 '13 at 13:50

Siddharth Rout

147,039
17
206
250

There is a razon to make the copy inside the cycle, 20 time, and not 1 time before the cycle? (Im learning too, and anyway +1) – qPCR4vir Apr 11 '13 at 18:41
@qPCR4vir: Yes there is a reason. Putting it inside the loop is a precaution. If any code runs say because of `Worksheet_Change` while pasting then it will clear the clipboard and you will get an error when you try to paste again. :) – Siddharth Rout Apr 11 '13 at 18:44

score 1 · Answer 2 · answered Apr 08 '13 at 19:16

Why not cut out the loop only if you don't have values below it you want to keep otherwise other answer are already provided:

Dim r As Range
Set r = Range("S16:Y16").resize(3)'changed range to include 2 rows bellow 

r(1,1).Offset(R.count, 0).resize(R.count*20).value = R.value

Please forgive any syntax errors as I'm on my mobile. I'm happy to fix if you find errors.

qPCR4vir · Answer 3 · 2013-04-08T14:06:34.393

Could you try this?

Sub pop1()
    '  Macro
    '
    ' Keyboard Shortcut: Ctrl+f
    '
    Dim r As Range
    Set r = Range("S16:Y16")
    r.Copy  

    For i = 1 To 20
        r.Offset(3 * i, 0).PasteSpecial
     Next i
End Sub

or this mininimalist:

Sub pop1()
    '  Macro
    '
    ' Keyboard Shortcut: Ctrl+f

  Range("S16:Y16").Copy

    For i = 1 To 20
        Range("S16:Y16").Offset(3 * i, 0).PasteSpecial
    Next i
End Sub

score -1 · Answer 4 · answered Apr 08 '13 at 14:13

-1

Try below code :

Sub pop1()
    '  Macro
    '
    ' Keyboard Shortcut: Ctrl+f
    '
    Dim rng As Range
    Set rng = Range("S16:Y16")

    j = 16
    For i = 1 To 20
        j = j + 3
         rng.Copy Range("S" & j)
    Next
End Sub

answered Apr 08 '13 at 14:13

Santosh

12,175
4
41
72

1

I was working on solution and when it was ready already there was an answer. I did noticed a very similar answer exist but as i dont wanted to waste my efforts i posted it. – Santosh Apr 08 '13 at 15:27
Yes I want it to paste 20 times then stop. – user2254486 Apr 08 '13 at 17:43
@glh Sometimes solution can be similar. If you term it as copying not cool. – Santosh Apr 09 '13 at 02:57
Thanks to everyone its working now. I am learning a lot thanks again – user2254486 Apr 10 '13 at 19:48

macro loop to copy paste hangs

4 Answers4