PDA

View Full Version : copy and paste data to certain cells



hunna
12-17-2011, 01:44 AM
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.


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

omp001
12-17-2011, 03:21 AM
Hi hunna.
try this:
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

hunna
12-17-2011, 04:31 AM
@ omp001 (http://www.vbaexpress.com/forum/member.php?u=41185)

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
12-17-2011, 04:47 AM
OK, try this. And also there won't be blank rows in the result table, as were in your original code
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

hunna
12-17-2011, 04:58 AM
thanks,

it works like a charm!!!