Why isn't my data pasting correctly when I copy and paste it within a For loop?

Question

I am trying to copy data from one sheet to another using VBA For loop. For some reason when I execute the below code the data is not being copied in one column under each other but in a strange format as shown below.

Sub Macro2()

For i = 110 To 116
For j = 1 To 1


Worksheets("overview of contracts").Activate
Range("A" & i).Select
Selection.Copy
Worksheets("Sheet1").Activate
ActiveCell.Offset(1, 0).Range("C" & j).Select
ActiveSheet.Paste

'Worksheets("overview of contracts").Activate
'Range("B" & i).Select
'Selection.Copy
'ActiveCell.Offset(1, 0).Range("D" & j).Select
'ActiveSheet.Paste

'Range("C" & i).Select
'Selection.Copy
'ActiveCell.Offset(1, 0).Range("E" & j).Select
'ActiveSheet.Paste

 Next j
Next i

End Sub

Result:

enter image description here

This is the original data, what I am trying to achieve is copy that data to another sheet using a loop exactly in that format and sequence. Sample Data

Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and all your problems will be over. Well not all of them, but this one at least. — SJR, Oct 17 '19 at 14:20
@Warcupine I know its stupid but I am very green with macros mate, would appreciate any help — Kylie James, Oct 17 '19 at 14:25
@KylieJames can you post a screen from your original sample and the desired output? Would help a lot. — Damian, Oct 17 '19 at 14:52
Another question, why are you looping through A110 to A116? you are not evaluating anything, why not just copying `Range("A110:A116")` and paste it to the other sheet, @KylieJames? — Damian, Oct 17 '19 at 15:04

score 1 · Accepted Answer · answered Oct 17 '19 at 14:57

I think this is what you want. It takes values in sheet("overview of contacts"), and copies their values to sheet("Sheet1") in column C, descending rows.

Sub Macro3()
    Dim i As Long
    Dim j As Long
    j = 2
    For i = 110 To 116
        Worksheets("Sheet1").Cells(j, 3).Value = Worksheets("Overview of Contracts").Cells(i, 1).Value
        j = j + 1
    Next i
End Sub

Why isn't my data pasting correctly when I copy and paste it within a For loop?

1 Answers1