Search code examples
excelexcel-2011vba

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

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. example screen shot

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 ;)

Solution

  • 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.