Excel Macro/VBA - Duplicate selected rows and adding repeating value

Question

I'm trying to automate some tedious work tasks of duplicating selected rows and then adding an extra value in each and I am stuck on the latter part.

As you can see below, I am able to duplicate the rows of my selection but when I go to add a concurrent value the offsetting doesn't line up.

Ideally I would have it copy the row, assign a size value and then repeat that for each size before moving onto a new style.

Anyone able to point me in the right direction? Here is where I'm at:

Dim i As Long

For i = (Selection.Row + Selection.Rows.Count - 1) To Selection.Row Step -1
    Rows(i).Copy
    Range(Rows(i + 1), Rows(i + 1)).Insert Shift:=xlDown

    Rows(i).Copy
    Range(Rows(i + 1), Rows(i + 1)).Insert Shift:=xlDown

    Rows(i).Copy
    Range(Rows(i + 1), Rows(i + 1)).Insert Shift:=xlDown

    Rows(i).Copy
    Range(Rows(i + 1), Rows(i + 1)).Insert Shift:=xlDown

    Rows(i).Copy
    Range(Rows(i + 1), Rows(i + 1)).Insert Shift:=xlDown


     ActiveCell.Offset(1, 2).Range("A1").Select
     ActiveCell.FormulaR1C1 = "X-small"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Small"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Medium"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Large"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "X-large"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "XX-Large"


Next i

End Sub

Initial Data Initial Data

Current result:

Current result

Desired outcome:

Ideal outcome

Wizhi · Accepted Answer · 2018-11-08 10:11:10Z

1

You could do something like this.

So I use an Array function to populate the size values, then we don't need any dummy columns.

VBA Code:

Sub RepeatRepeat()

Dim myArrayVal()
Dim i As Long
Dim j As Long
Dim k As Long
Dim lrow As Long
Dim lrow2 As Long

myArrayVal() = Array("X-Small", "Small", "Medium", "Large", "X-Large", "XX-Large")

lrow = Cells(Rows.Count, 1).End(xlUp).Row 'find last row in Column A

j = 2
For j = 2 To lrow
    lrow2 = Cells(Rows.Count, 9).End(xlUp).Row + 1 'Find last row in column I
    For k = LBound(myArrayVal) To UBound(myArrayVal) 'Loop Array
        Cells(lrow2, 9).Value = myArrayVal(k) 'Print Array value
        Cells(lrow2, 8).Value = Cells(j, 2).Value 'Copy from Column B to Column H
        Cells(lrow2, 7).Value = Cells(j, 1).Value 'Copy from Column B to Column H
        lrow2 = lrow2 + 1 'Add one to last row
    Next k
Next j
End Sub

Result:

answered Nov 8, 2018 at 10:11

Wizhi

6,5894 gold badges29 silver badges49 bronze badges

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

1 Comment

Hayden Davey Over a year ago

Sorry, my explanation of what I was hoping to accomplish wasn't clear. I just edited my question to hopefully get my intention across better. The functionality I am looking for is to be able to select multiple rows within a sheet and extrapolate/insert new rows with size values

Collectives™ on Stack Overflow

Excel Macro/VBA - Duplicate selected rows and adding repeating value

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