Copying a range in a row where the # of x to be copied is in cell

Question

I need to copy each of 69 rows n number of times. The n number of times each range in a row that should be copied is in the row as well. I attached a screen shot so you can see the data. I tried one of the other answers on here, but it did not work for me.

So, in the above screen shot, I'd like to have B2, D2:G2 copied 284 times. As I am typing this, I can see this will work better if I switch column C and B.

Anyhow - I have seen some examples with VBA. I am not familiar with it, but I am not unfamiliar with coding in general. So, if the solution is VBA, then I am up for it, I just need instructions for a dummy level ;)

Could you add some more details such as where you want to paste the data, want you have tried so far, and which part of the task you're stuck with? — Jane, Jul 29 '15 at 17:41
@Jane - I tried a formula that involved creating a column that calculated the end row number for the data and VLOOKUP, but it did not return anything other than one broken cell (rather than cells n number of times). The data could be pasted in a second sheet or even in the same sheet. I am fine to c&p paste it into a new file in the end if need be. — shannimcg, Jul 29 '15 at 17:44

Jane · Accepted Answer · 2015-07-29T19:12:59.090

1

In Excel, you can copy some data which is 1 row by multiple columns (eg B5:Z5), then paste that data in to a range which is 1 column wide by multiple rows (eg D10:D50) and the data will be repeated in each row. That's what the code below is doing:

Sub MultiCopy()
    Dim sourceRange As Range
    Dim targetBook As Workbook
    Dim targetRange As Range
    Dim cell As Range
    Dim count As Integer
    Dim copyRange As Range

    'Adjust this range to reflect the list containing the numbers
    Set sourceRange = ActiveSheet.Range("B2:B6")

    'We'll copy the data to a new workbook
    Set targetBook = Workbooks.Add
    Set targetRange = targetBook.Worksheets(1).Range("A1")

    'Loop through the cells which contain the number of times to repeat the row
    For Each cell In sourceRange
        'Get the number of times that the current row will be repeated
        count = cell.Value
        'The range being copied starts in the cell to the right of the value, and is 5 cells wide
        Set copyRange = cell.Offset(0, 1).Resize(1, 5)
        'The data will be pasted in to a vertical block of cells
        'The copied data gets repeated in each row
        Set targetRange = targetRange.Resize(count, 1)
        copyRange.Copy targetRange
        'Reset the targetrange ready for the next row
        Set targetRange = targetRange.Offset(count, 0)
    Next
End Sub

I don't have a Mac, but the code below works fine on Excel 2013, and I'm not aware of any reason that it wouldn't work on a Mac.

edited Jul 29 '15 at 19:12

answered Jul 29 '15 at 18:29

Jane

851
4
9

Thank you! Before I try it, I have a few questions. Will the copied data retain its column breakdown? Each column is a variable, so I need the copied data to still in columns. Also, where in the code does the number of times I want the row copied come in? I'm sure it's there, I just don't see whether it's reading it off a cell in the data (which I have) or if I am manually inputting it (which I can also do). Thank you! – shannimcg Jul 29 '15 at 18:50
This will keep each row of data that is copied in the same layout (ie in the sample if copies a block which is 1 cell high and 5 cells wide, so when it is pasted it will still be 5 cells wide). – Jane Jul 29 '15 at 19:07
The number of times it will be pasted comes from count = cell.value. The code loops through the cells in the specified range (B1:B6 in this case), and gets the value from that cell. Count is then used to set the number of rows that the data is pasted in to (targetRange.Resize) and to reset the targetRange for the next block of data (targetRange.Offset) – Jane Jul 29 '15 at 19:10
@shannimcg I've updated the comments - is that a little clearer? – Jane Jul 29 '15 at 19:13
Yes, I think that makes more sense. I am going to try it now. First time doing VBA - thank you so much. Will update with results! – shannimcg Jul 29 '15 at 19:25

Copying a range in a row where the # of x to be copied is in cell

1 Answers1