Copy and Paste row 5- row 9 n number of times [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I am trying to save size of my Workbook from blowing of, which has lots of vlookup's and other formulas.

So, i am trying to copy row 5- row 9 n number of times.

5. Row1
6. Row2
7. Row3
8. Row4
9. Row5

i am trying to get when i enter n = 2, it pastes row 5-9 twice below row 9.

5. Row1
6. Row2
7. Row3
8. Row4
9. Row5
10. Row1
11. Row2
12. Row3
13. Row4
14. Row5
15. Row1
16. Row2
17. Row3
18. Row4
19. Row5

I would appreciate you help with this.

Thank you.

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
your data from 5th row is like this





Row1 x x x x
Row2 y y y y
Row3 z z z z
Row4 a a a a
Row5 b b b b

try this macro


Sub test()
Dim r As Range, j As Long, k As Long, dest As Range
Application.ScreenUpdating = False

j = 5
Set r = Range(Range("A5"), Range("A5").End(xlToRight).End(xlDown))
Set dest = Range("A15")
dest.CurrentRegion.Cells.Clear
k = 1
Do
r.Copy dest
k = k + 1
Set dest = dest.Offset(5, 0)
If k = j + 1 Then Exit Do
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "copying done"
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3794 users have said thank you to us this month