copy and paste data to certain cells

[hunna]

I have a code to copy data from cells(i, xx) to different column but same row.


I would like to copy and always paste the data starting from row 2.

any help would be appreciated.


[vba]Sub test()
Dim i As Long
For i = 1 To 30


If Not Cells(i, 11) = "" Then

Cells(i, 1) = Cells(i, 11).Value
Cells(i, 2) = Cells(i, 12).Value
Cells(i, 3) = Cells(i, 13).Value
Cells(i, 4) = Cells(i, 14).Value
Cells(i, 5) = Cells(i, 15).Value
Cells(i, 6) = Cells(i, 16).Value
Cells(i, 7) = Cells(i, 17).Value


Else

End If

Next i

End Sub
[/vba]

[omp001]

Hi hunna.
try this:
[vba]Sub test()
Dim i As Long
For i = 1 To 30
If Not Cells(i, 11) = "" Then
Cells(i, 11).Resize(, 7).Copy Cells(i + 1, 1)
End If
Next i
End Sub[/vba]

[hunna]

@ omp001

Thanks for the code.

I still have some problem because data in column 11 can be in any row so from the code, data is not always pasted in row 2 of new column.

[omp001]

OK, try this. And also there won't be blank rows in the result table, as were in your original code
[vba]Sub test()
Dim i, k As Long
k = 2
For i = 1 To 30
If Not Cells(i, 11) = "" Then
Cells(i, 11).Resize(, 7).Copy Cells(k, 1)
k = k + 1
End If
Next i
End Sub[/vba]

[hunna]

thanks,

it works like a charm!!!