CODEWITHSUNDEEP
excelvbaloopscopypaste
user3578485
user3578485

Reputation: 3

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

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

Upvotes: 0

Views: 6322

Answers (1)

Cor_Blimey
Cor_Blimey

Reputation: 3310

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

Upvotes: 2

Related Questions