Creating a Copy/Paste VBA loop

Question

I have a massive data set (1000s of values) in a large spreadsheet, and I need to copy and paste these values into a new sheet in sets of 10. Essentially I need to copy the first 10 cells in a column and paste them into their own column in a new sheet. I then need to repeat this with cells 11-20 and paste them into their own column in a new sheet. So: copy 1-10, paste into new sheet in column A. Then copy 11-20 and paste into same new sheet, column B. 21-30 in column C, and so on.

I have already tried some basic code, I am really new to this but find it interesting. I have figured out how to copy a range of 10 and paste to a new sheet, but I do not know how to set the loop up so I can continue this process into new columns with increasing sets of 10. I also have tried recording a macro, however it just recreates literally the keystrokes I input which isn't useful to me.

I wrote this to copy first 10 in my source column and paste to my new sheet in their new column.

Option Explicit
Sub CopyCells()
Worksheets("Sheet1").Range("A3:A12").Copy Worksheets("Sheet2").Range("B2:B11")
End Sub

I need it to loop and continuously cut and paste sets of 10 cells from one target column into a new sheet, and each set of 10 into their own new column. If you post an answer could you please add explanations as well? I really like this stuff and am trying to learn, not just copy paste some code. Thanks!

Tim Williams · Accepted Answer · 2019-08-21 21:32:42Z

1

You can do something like this:

Sub CopyCells()
    Const NUM_ROWS As Long = 10  'size of block to copy
    Dim rngCopy As range, col As Long

    Set rngCopy = Worksheets("Sheet1").Range("A3").Resize(NUM_ROWS, 1)
    col = 2
    'copy while rngCopy has any data
    Do While Application.Counta(rngCopy) > 0
        rngCopy.Copy Worksheets("Sheet2").Cells(2, col)
        col = col + 1                       'increment destination column
        Set rngCopy = rngCopy.Offset(NUM_ROWS, 0) 'move copy range down 10
    Loop

End Sub

answered Aug 21, 2019 at 21:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Heiler Over a year ago

Thank you Tim! I think this worked! I really really appreciate your quick help. I was trying to find a way to do a Do While loop until the column was empty because it seemed like the best thing to do, but I hadn't quite figured out how to add 10 to the range. This is so helpful seriously thank you

Collectives™ on Stack Overflow

Creating a Copy/Paste VBA loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related