Selecting ten cells and pasting them to new sheet horizontally in i loop

cwest530 · Aug 2nd 2016

I am trying to get a loop function to select ten cells in column C and then paste those selected columns to sheet 4 and paste them horizontally, the problem I am encountering is instead of copying the ten cells in one column it selects one cell in the column and copy's it ten times in sheet 4. I realize this is because of how my code is set, my question is how can I make vba select the the ten cells then paste it on the other sheet and then have vba select the next ten cells in the column? This is what I have:

Code

Sub Transfer()








Dim i, r, LastRow












LastRow = Sheets("sheet1").Range("C" & Rows.Count).End(xlUp).Row








For i = 2 To LastRow




    For r = 1 To 10
    
         Sheets("sheet1").Cells(i, "C").Copy Destination:=Sheets("sheet4").Range("A" & Rows.Count).End(xlUp).Offset(i, r)
         
        
    Next r




Next i




End Sub

Display More

Any tips and or suggestions would be most appreciated, if it is not clear just let me know and I can try to rephrase my question. Thanks

**alansidman** · Aug 3rd 2016

Re: Selecting ten cells and pasting them to new sheet horizontally in i loop

If you are copying all the cells in the column (i = 2 to last row), then why do it in increments of 10. Why not just copy all the data in column C and paste it into Sheet 4. Am I misunderstanding your needs? Please expand on your explanation and explain the rationale of what you are trying to accomplish. The actual Business scenario.

Edit: Just re-read this. Are trying to transpose the 10 items when pasting them into the new sheet? And each 10 items are on a new line?

**alansidman** · Aug 3rd 2016

Re: Selecting ten cells and pasting them to new sheet horizontally in i loop

Try this:

Code

Option Explicit




Sub Trnspse()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet4")
    Dim lr As Long
    Dim lr2 As Long
    Dim i As Long
    lr = s1.Range("C" & Rows.Count).End(xlUp).Row




    For i = 2 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        With s1
            .Range("C" & i & ":C" & i + 9).Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteAll, , , True
        End With
        i = i + 9
    Next i




End Sub

Display More

cwest530 · Aug 3rd 2016

Re: Selecting ten cells and pasting them to new sheet horizontally in i loop

Thanks that worked, I needed a piece of code that spat out lines of ten and went downwards because the way another program reads excel files for printing labels for shipping. This code will generate a list of numbers in increments of ten, ten numbers = ten labels = one lot. Thus automating operations, for one of my coworkers. Making everything more efficient was the business scenario.

Selecting ten cells and pasting them to new sheet horizontally in i loop

Participate now!

Share