Loop through range and copy cell contents to another worksheet offsetting each time

Question

Not having any luck finding a solution to this problem. Working with loops.

The main concept is that my macro takes values from one column of cells (one at a time) and pastes them to another column in another worksheet except each time the values getting pasted are 7 cells down from the last paste. - Thanks for considering my request.

Sub LoopData()

    Dim X As Integer
    NumRows = Range("A1", Range("A2").End(xlDown)).Rows.Count

    Range("A1").Select
    For X = 1 To NumRows

    Selection.Copy _
        Destination:=Worksheets("Sheet1").Range("A1")

    'not sure for what code to put here to move the copied contents down 7 cells each time    

    ActiveCell.Offset(1, 0).Select
    Next 
End Sub

1) [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) 2) [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) 3) each time you paste values, determine last row and add 7 — Dmitry Pavliv, Apr 27 '14 at 15:19
**Do you want the lasted material at the top or the bottom of the destination??** — Gary's Student, Apr 27 '14 at 15:41
At the top. The cell contents need to paste in decending order. With a gap of 7 cells between each. Right now the way I have it written the cell contents get written to the same cell each time. I need it to write 7 rows below each time until the loop is finished. — user3578485, Apr 27 '14 at 15:44

score 2 · Accepted Answer · answered Apr 27 '14 at 15:58

You can do something using .Offset like:

Sub blah()
    Dim inRng As Range, outCell As Range, inCell As Range

    Set inRng = Selection 'change
    Set outCell = Range("B1") 'change to be first cell of output range

    Application.ScreenUpdating = False

    For Each inCell In inRng
        inCell.Copy outCell 'if you want Values only (instead of Copy) then use outCell.Value = inCell.Value
        Set outCell = outCell.offset(7, 0)
    Next inCell

    Application.ScreenUpdating = True

End Sub

Yes, thank you @Cor_Blimey. That worked perfect. Thanks for your efforts. — user3578485, Apr 27 '14 at 16:25

Loop through range and copy cell contents to another worksheet offsetting each time

1 Answers1